Transact-SQL (T-SQL) is a dialect of SQL used by Microsoft SQL Server and SAP's Sybase.

learn more… | top users | synonyms

6
votes
2answers
549 views

Does MERGE use tempdb?

Consider the following query: MERGE [Parameter] with (rowlock) AS target USING (SELECT @AreaId, @ParameterTypeId, @Value) AS source (AreaId, ParameterTypeId, Value) ON ...
6
votes
1answer
4k views

Find all ancestor nodes of a HierarchyId using a single SQL statement?

I'm trying to find a way to get all ancestor nodes of a given node using HierarchyID. Every solution I've seen using HierarchyID seems to use either a CTE or a variable. Is there a way to do this ...
6
votes
1answer
135 views

What is the difference between <> All and Not In

Could you please tell me the differences between select CustomerId from Customer Where TerritoryId <> All(select TerritoryId from Salesperson) select CustomerId from Customer where TerritoryId ...
6
votes
2answers
3k views

How to optimise T-SQL query using Execution Plan

I have a SQL query that I have spent the past two days trying to optimise using trial-and-error and the execution plan, but to no avail. Please forgive me for doing this but I will post the entire ...
6
votes
3answers
2k views

Can I change table structure in a transaction and then roll it back if there is an error?

I have some ALTER TABLE statements that I am running. Not all of them work (they are the result of running SQL Data Compare) and I want to group them in some transactions and roll back the statements ...
6
votes
1answer
238 views

Sargable Queries

Very simple question I hope, I'm currently having some scripts I inherited into my project reviewed by the database admins. This has never been done before apparently and lots of issues are being ...
6
votes
4answers
3k views

T-SQL command to log in as a different user?

Is there a T-sql command, like the Oracle "Connect" which lets me change the user that I am logged in as?
6
votes
3answers
3k views

RETURN statement before DEALLOCATE CURSOR

Question about use of Cursors in combination with RETURN in a SQL Server 2008 Stored Procedure Consider this example: CREATE PROCEDURE [dbo].[test] @ReturnEarly BIT = 0 AS BEGIN SET NOCOUNT ...
6
votes
2answers
768 views

Is there a better way to create dynamic time averaged series over a dynamic frequency?

I've got a series of tables with lots of high precision data collected from various devices. The intervals that they were collected on varies and even wanders over the time series. My users want the ...
6
votes
1answer
2k views

Restore a Database to a New database (T-SQL)

I have a database backup from a database, say SourceData which needs to be restored on another database, same schema, but different name (SourceData_1) using T-SQL. I don't know why I gets the ...
5
votes
5answers
5k views

Why can't I use a CASE statement to see if a column exists and not SELECT from it?

Why does something like this not work? SELECT CASE WHEN NULLIF(COL_LENGTH('Customers', 'Somecol'), '') IS NULL THEN NULL ELSE Somecol END AS MyTest FROM Customers; I am just checking if the column ...
5
votes
3answers
3k views

Choosing the right algorithm in HashBytes function

We need to create hash value of nvarchar data for comparison purposes. There are multiple hash algorithms available in T-SQL, but which one the best to choose from in this scenario? We want to ensure ...
5
votes
1answer
523 views

Use of FROM keyword on DELETE statement

The BOL and many other sources point out: FROM Is an optional keyword that can be used between the DELETE keyword and the target table_or_view_name, or rowset_function_limited. I'm used to ...
5
votes
2answers
625 views

T-SQL Purpose of MAX in this Group By Query

I have run into some pre-existing SQL that I'm having a hard time uderstanding. SELECT MAX(I.Symbol) Symbol , MAX(I.Ticker) CUSIP , MAX(I.Name) Name , SUM(H.Quantity) TotalQuantity , ...
5
votes
4answers
990 views

Speeding up COUNT(*) - WHERE clause slowing query

The following query takes 497 ms to run, if I remove the AND portion onwards it only takes 320 ms. Is there any way to speed this up? The only indexes I have on 'messages' table is a PK on ...
5
votes
1answer
388 views

Optimize where clause using dates

I'm trying to optimize several queries that all use a similar pattern on one of the WHERE clauses: AND (DATEADD(DAY , ISNULL(a.[due_days], 30) + 30 , [dbo].[CalcDate]([type], ...
5
votes
1answer
176 views

Can We Perform an “Up-To-The-Minute Restore” After Taking Transaction Log Backups?

The Setup: Say we have a Database in Full Recovery Mode that does a nightly Database Backup, Transaction Log Backup, and Transaction Log Shrink. Now the Transaction Log grows so quickly during the ...
5
votes
1answer
360 views

How to avoid a race condition with cross db proc calls? and other concerns

I am not even sure this question is necessary but I am curious to know everyone's thoughts. I have two databases on the same server, dbFoo, dbBar. dbFoo has the following table please note this is a ...
5
votes
2answers
173 views

In the SQL Server RESTORE statement, in what time zone is the STOPAT argument?

SQL Server can perform point-in-time restores using the STOPAT argument. In what time zone is that date and time value? Is it UTC? Is it local for the server the backup was taken? Is it local for ...
5
votes
2answers
2k views

The order of INNER JOIN's

I would like to know if there are any rules about the order of INNER JOIN's in T-SQL 2000. I had 62x the performance on my query when my INNER JOIN that act like a filter is placed at first instead ...
5
votes
3answers
2k views

Which problems arise, declaring the size of all varchar parameters as max in stored proc?

Looking at this question, it seems, that other sites too have the problem of increasing column sizes in the process of time. Increasing just the size of a column in a table is a rather simple task. ...
5
votes
1answer
232 views

View designer strange join syntax

I have a data warehouse with a lot of views built in with the SSMS view designer. Viewing the syntax, the FROM clauses are unintelligible to me because the ON statements do not follow straight after ...
5
votes
1answer
279 views

difference of left join and left outer join

In SQL Server join syntax we can use LEFT JOIN or LEFT OUTER JOIN and query result with each of above join not difference. this is a question for me that which are more useful.
5
votes
2answers
946 views

How to change SQL sever configuration manager settings using TSQL?

SQL server configuration manager is used to configure certain settings like connection protocols, service start up, etc... Is it possible to make these changes that is done in SQL server ...
5
votes
2answers
164 views

'+' operator with one operand!

How does '+' operator behave in following statement? select + 'taco'; --Result is 'taco' Is it doing string concatenation with first string blank ('' + 'taco'), or does it mean something else?
5
votes
1answer
338 views

Passing a literal ? to sp_MSforeachdb

I am trying to search for the ? character in a string, but across all dbs on a server. Since the ? mark is a placeholder for the database name, how may I format the query so it knows I mean to search ...
5
votes
1answer
501 views

Splitting a csv column from select query into multiple columns

I have a stored procedure SELECT [ShipWorks].[dbo].[OrderItem].[Code] AS Code, CASE WHEN (LEN(ISNULL(MAX([ShipWorks].[dbo].[OrderItem].[Location]),'')) = 1) THEN ...
5
votes
1answer
92 views

SMS Suggesting covering all columns in an index?

I'm still a bit newer to indexes and database works, so I may very well be missing something obvious. SMS suggested adding a covered index to one of my tables that Indexes on the column I believe ...
5
votes
1answer
3k views

How to changes or update local server connection in Maintenance plan job

Two days back our client changed one of our Dev Server name After Server Renamed, All my maintenance jobs and other jobs are failing because server name mismatch. We are using sql server 2012 ...
5
votes
1answer
406 views

Unexpected Table Scan with Parameterized LIKE

I'm experiencing an unexpected table scan on SQL Server 2005 against a heap table when parameterizing a LIKE statement... but when the same value as the variable is hard-coded, the expected Index Seek ...
5
votes
3answers
1k views

Total of orders open on a given date for each date in a date range

This question is similar to Running total with count?, but please allow me to explain some further twists on the issue. I'm using SQL Server 2008, so the cursor option described by Aaron Bertrand ...
5
votes
2answers
11k views

How can I strip non-numeric characters out of a string?

Users enter a search term in a box, and that value gets passed to a stored procedure and checked against a few different fields in the database. These fields are not always of the same data type. One ...
5
votes
1answer
1k views

Using FLOATs with RAISERROR

I am using RAISERROR() to provide some basic Unit Testing functionality (as here), but I am frustrated by the inability to use FLOATs in the error message. I know I can cast the float to a string, but ...
5
votes
2answers
4k views

Grouping records based on intervals of time

I have a table with the following schema, and I need to define a query that can group data based on intervals of time (Ex. records per minute) and then provide the sum of the changes to the ...
5
votes
3answers
745 views

How do I row count into groups?

I have records with a bit field that are in an order how do I group them in sets of 3. For example if my first three rows have bit fields 0,1,0 I want them in set 0, the next 3 are 1,0,0 I want them ...
5
votes
1answer
572 views

Why can the target of the OUTPUT INTO clause not be a view?

Here is my statement: DELETE FROM table1 OUTPUT deleted.col1, deleted.col2 INTO view1; It gives me this error: The target 'view1' of the OUTPUT INTO clause cannot be a view or common table ...
5
votes
2answers
683 views

Efficient query for an advanced EAV model

I'm kind of stuck on how to build an effecient query which returns the data of the following EAV structure. Today, a product table already exists, containing 4 fixed fields. We want to upgrade the ...
5
votes
1answer
1k views

Why does SQL Server 2008 R2 insert an invalid datetime when using bulk insert?

I want to insert rows with an optional datetime value (per row) using T-SQL BULK INSERT on a SQL Server 2008 R2. A table could look like this: CREATE TABLE [dbo].[tbl_bulk_insert_datetime_issue] ( ...
5
votes
1answer
2k views

How to detach corrupt database in SQL Server

I have Sql server data files saved on external storage. I want to detach specific database. If external storage is not connected I want to drop database. Is this safe way to do that: DECLARE ...
5
votes
4answers
16k views

Add a new Column and define its position in a table

I have Table A with 5 columns: TableA -- Name Tel Email Address I want to add a new column (mobile) in between Tel & Email: TableA -- Name Tel Mobile Email Address If I use ALTER TABLE ...
5
votes
1answer
618 views

Query performance while using sqlcmd [duplicate]

Possible Duplicate: Running queries with SQLCMD vs. Running queries with SSMS I have a number of scripts that query the data from different tables and consequently update them. I have ...
4
votes
5answers
1k views

How can I prove that the following T-SQL is bad for performance?

I have recently inherited a codebase with a large amount of Stored Procedures. The system they are supporting is encountering numerous performance problems which I am looking in to. A number of the ...
4
votes
6answers
143 views

Select rows with same id but null and some other value in another column for that id

I want to get only rows having a value NULL and some other value than NULL for a particular username column. If both rows have null for that particular username or both have some values other than ...
4
votes
2answers
473 views

Escaping T-SQL Keywords

$sql = "SELECT Kill FROM tbl_pvporderview"; Problem is that I end up with: Incorrect syntax near the keyword 'Kill'. Because kill is a T-SQL command... any way to bypass it? I can't change the ...
4
votes
4answers
504 views

How does SQL Server determine precision / scale?

I'm running SQL Server 2012 SELECT 0.15 * 30 / 360, 0.15 / 360 * 30 Results: 0.012500, 0.012480 This one is even mor confusing to me: DECLARE @N INT = 360 DECLARE @I ...
4
votes
3answers
438 views

Is it advisable to use a temp table when the stored procedure could be run simultaneously by different users?

I am working on a stored procedure that retrieves the objectGUID from active directory. I am storing the result in a temp table and then returning the value in an output parameter for use with other ...
4
votes
2answers
128 views

Difference Between SP with BEGIN/END and without BEGIN/END

I have found some SP(stored procedure) written as, CREATE PROCEDURE [dbo].[XXX] ( -- Parameters ) BEGIN --- Actual Work END and some as CREATE PROCEDURE [dbo].[XXX] ( -- Parameters ) --- ...
4
votes
1answer
3k views

SQL Server 2012 is putting [brackets] around Table and Column Names

Is there anyway to turn that off? I'm unable to find anything in options, and they're really a distraction.
4
votes
4answers
12k views

T SQL Table Valued Function to Split a Column on commas

I wrote a Table Valued Function in Microsoft SQL Server 2008 to take a comma delimited column in a database to spit out separate rows for each value. Ex: "one,two,three,four" would return a new table ...
4
votes
2answers
7k views

A Script to insert dummy Data in all tables of Database

I wanted a Script that can loop through all tables and their columns in the database and insert dummy data based on column type and size, so that i can start using the database for testing, and ...