Questions tagged [t-sql]

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

Filter by
Sorted by
Tagged with
103 votes
6 answers

Retrieving n rows per group

I often need to select a number of rows from each group in a result set. For example, I might want to list the 'n' highest or lowest recent order values per customer. In more complex cases, the ...
Paul White's user avatar
  • 83k
482 votes
2 answers

What's the difference between a temp table and table variable in SQL Server?

This seems to be an area with quite a few myths and conflicting views. So what is the difference between a table variable and a local temporary table in SQL Server?
Martin Smith's user avatar
  • 83.3k
10 votes
5 answers

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 ...
OvetS's user avatar
  • 103
39 votes
5 answers

Logical operators OR AND in condition and order of conditions in WHERE

Let's examine these two statements: IF (CONDITION 1) OR (CONDITION 2) ... IF (CONDITION 3) AND (CONDITION 4) ... If CONDITION 1 is TRUE, will CONDITION 2 be checked? If CONDITION 3 is FALSE, will ...
garik's user avatar
  • 6,702
35 votes
7 answers

How can I tell if a SQL Server database is still being used?

We're looking to decommission a SQL Server instance which has a couple databases still remaining on it. How can I tell if they are still being used by users or a web application? I found a forum ...
jsauni's user avatar
  • 1,040
17 votes
1 answer

Are we required to handle Transaction in C# Code as well as in stored procedure

Do we really require transaction handling in c# as well database store process both side C#: Using(transaction with transaction scope) { Execute stored proc; Transaction. Complete; } SQL ...
Rakesh Gaur's user avatar
27 votes
2 answers

What does the position of the ON clause actually mean?

The normal JOIN ... ON ... syntax is well known. But it is also possible to position the ON clause separately from the JOIN that it corresponds to. This is something that is rarely seen in practice, ...
boot4life's user avatar
  • 1,289
16 votes
4 answers

Is a join optimized to a where clause at runtime?

When I write a query like this... select * from table1 t1 join table2 t2 on = Does the SQL optimizer, not sure if that is the correct term, translate that to... select * from table1 t1, ...
Mr. Ant's user avatar
  • 263
3 votes
1 answer

Conversion of varchar data to datetime failing

We are in the process of moving data from a legacy table (all varchar fields) into a strongly typed counterpart cue cheering As part of this effort, we are taking data from the base Entity table and ...
billinkc's user avatar
  • 15.8k
63 votes
6 answers

Date range rolling sum using window functions

I need to calculate a rolling sum over a date range. To illustrate, using the AdventureWorks sample database, the following hypothetical syntax would do exactly what I need: SELECT TH.ProductID, ...
Paul White's user avatar
  • 83k
40 votes
1 answer

GO After every T-SQL statement

What is the reasoning behind using the GO statement after every SQL statement? I understand that GO signals the end of batch and/or allows the reputation of statements but what advantage does it have ...
TheIdiot's user avatar
  • 503
16 votes
3 answers

Why should a CTE start with a semi-colon?

I was just looking at a post on StackOverflow where Aaron Bertrand proposes using a CTE instead of a numbers table, which is an elegant way of performing the task at hand. My question is, why does ...
Hannah Vernon's user avatar
  • 69.7k
10 votes
2 answers

What is the most efficient way to concatenate strings in SQL Server?

I have this code: DECLARE @MyTable AS TABLE ( [Month] INT, Salary INT ); INSERT INTO @MyTable VALUES (1,2000), (1,3100); SELECT [Month], Salary FROM @MyTable; Output: I want to concat the ...
Misha Zaslavsky's user avatar
9 votes
2 answers

Can I get a tree-structure from a self-referenced (hierarchical) table?

Given a hierarchical table like this: CREATE TABLE [dbo].[btree] ( id INT PRIMARY KEY , parent_id INT REFERENCES [dbo].[btree] ([id]) , name NVARCHAR(20) ); I would like to obtain the whole tree ...
McNets's user avatar
  • 23.6k
7 votes
4 answers

SQL Server - Alter table vs drop and create

Our database is SQL Server 2008 R2. We have some tables that have some varchar(500) columns that I want to switch to datetime2 or bigint. I can guarantee all the data in the columns to be switched are ...
scottr's user avatar
  • 83
42 votes
2 answers


I have some auto-generated T-SQL, which is probably valid, but I don't really understand. ALTER TABLE [dbo].[MyTable] WITH CHECK CHECK CONSTRAINT [My_FORIEGN_KEY]; I know what a foreign key ...
BanksySan's user avatar
  • 981
34 votes
3 answers

What does OPTION FAST in SELECT statement do?

I have done some digging on what the OPTION (FAST XXX) query hint does inside a SELECT statement and I'm still confused about it. According to MSDN: Specifies that the query is optimized for fast ...
Matthew Verstraete's user avatar
32 votes
2 answers

How to use COALESCE with multiple rows and without preceding comma?

I'm trying to achieve the following: California | Los Angeles, San Francisco, Sacramento Florida | Jacksonville, Miami Unfortunately, I'm getting ",Los Angeles, San Francisco, Sacramento, ...
user2732180's user avatar
23 votes
4 answers

Is there a way to generate table create script in TSQL?

Is there a way to generate a create script from an existing table purely in T-SQL (that is without using SMO, since T-SQL does not have access to SMO). Let's say a stored procedure that receives a ...
Andrew Savinykh's user avatar
10 votes
3 answers

sql server: updating fields on huge table in small chunks: how to get progress/status?

We have a very large (100million row) table, and we need to update a couple of fields on it. For log shipping, etc, we also, obviously, want to keep it to bite-size transactions. Will the below do ...
Jonesome Reinstate Monica's user avatar
8 votes
1 answer

Inserting with implicit type conversion causes warning for cardinality estimates

I noticed this while doing some performance testing recently. When I insert a value into a column that will require an implicit conversion (e.g. bigint into nvarchar), I get a warning: Type ...
Dan Oberlam's user avatar
3 votes
1 answer

Implementing a PIVOT query

How can I pivot the following SELECT statement: SELECT sc.CID, sc.CodeName as OverviewText, scRAG.CodeName as RAGStatusText FROM StatusCode ...
kacalapy's user avatar
  • 2,032
0 votes
1 answer

AlwaysON Availability group query very slow

I am running below tsql to find the secondary AlwaysON servers from the primary replica : set transaction isolation level read uncommitted set statistics time, io on SELECT ISNULL(agstates....
Kin Shah's user avatar
  • 62.1k
79 votes
1 answer

MERGE a subset of the target table

I am trying to use a MERGE statement to insert or delete rows from a table, but I only want to act on a subset of those rows. The documentation for MERGE has a pretty strongly worded warning: It is ...
KutuluMike's user avatar
  • 1,589
65 votes
4 answers

Does SQL Server CASE statement evaluate all conditions or exit on first TRUE condition?

Does the SQL Server (2008 or 2012, specifically) CASE statement evaluate all the WHEN conditions or does it exit once it finds a WHEN clause that evaluates to true? If it does go through the entire ...
Juan Velez's user avatar
  • 3,245
53 votes
4 answers

What's the easiest way to create a temp table in SQL Server that can hold the result of a stored procedure?

Many times I need to write something like the following when dealing with SQL Server. create table #table_name ( column1 int, column2 varchar(200) ... ) insert into #table_name execute ...
Just a learner's user avatar
42 votes
4 answers

Table-Valued Parameter as Output parameter for stored procedure

Is it possibile to Table-Valued parameter be used as output param for stored procedure ? Here is, what I want to do in code /*First I create MY type */ CREATE TYPE typ_test AS TABLE ( id int ...
adopilot's user avatar
  • 2,403
32 votes
3 answers

Why do wildcards in GROUP BY statements not work?

I am trying to make the following SQL statement work, but I get a syntax error: SELECT A.*, COUNT( FROM TABLE1 A LEFT JOIN TABLE2 B ON A.PKey = B.FKey GROUP BY A.* Here, A is a wide table with ...
Anonymous Maximus's user avatar
19 votes
5 answers

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 ...
Carson Reinke's user avatar
16 votes
3 answers

Why does "SELECT POWER(10.0, 38.0);" throw an arithmetic overflow error?

I'm updating my IDENTITY overflow check script to account for DECIMAL and NUMERIC IDENTITY columns. As part of the check I compute the size of the data type's range for every IDENTITY column; I use ...
Nick Chammas's user avatar
  • 14.6k
16 votes
2 answers

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 ...
Neo's user avatar
  • 435
15 votes
3 answers


Recently we used a sql code reviewing tool against our database. It is suggesting to use SP_EXECUTESQL instead of EXEC. I know SP_EXECUTESQL helps us to avoid sql injection. Is there any difference ...
Pரதீப்'s user avatar
10 votes
1 answer

How to extract the last inserted row in SQL Server?

When an insert statement is executed one or more rows is inserted into the table, is there any way to extract the last inserted row in SQL Server?
user2493976's user avatar
10 votes
2 answers

Joining multiple tables results in duplicate rows

I am getting more rows then I expect returned by my query. I believe it has something to do with my join statements. There are multiple tables that have different information in them. Person ...
normandantzig's user avatar
8 votes
1 answer

Is it "legal" to CREATE and DROP #SomeTable more than once?

I've got my code sort of segregated as "coherent blocks" that I can insert into a longer "configuration script" over and over, and one of the patterns I'm using is this: CREATE TABLE #WidgetSetting (...
jcolebrand's user avatar
  • 6,345
6 votes
5 answers

Creating temporary table dynamically using condition

I am trying to create a temporary table dynamically using the below code, and I know we can't have two create statements for the same table. Is there a workaround for this to put it in a conditional ...
Biju jose's user avatar
  • 2,063
6 votes
2 answers

Which is better for long term database scalability: adding columns or having key/value store

For application, which has few (5-10) settings at the moment but will have a lot more (up to 100) in the future which would be better approach: to add new columns to settings table every time new ...
valentinas's user avatar
5 votes
1 answer

How to set identity_insert to on in SQL Server

I get the command set identity_insert table_name on;, but for some reason it is not actually changing the attribute. I've done it with the account that I created the table with, and I've also created ...
Joe Essey's user avatar
  • 351
5 votes
1 answer

Invalid use of a side-effecting operator 'INSERT' within a function - Multiple Inserts in Function

If this is not the right forum please let me know and move it for me. I've just got a bunch of inserts like this toward the end of my scalar function: INSERT INTO [Raptor].[dbo].[UserRole] ...'s user avatar
4 votes
2 answers

How do I delete unique rows from a table with duplicate keys?

I have the following data: road | ID | other column | other column | ------------------+------+---------------+--------------+ MAIN ST | 1 | ... | ...
Conor's user avatar
  • 155
4 votes
2 answers

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 ...
AmmarR's user avatar
  • 2,776
3 votes
2 answers

T-SQL query for date of last full backup, size and location

I'm using the below T-SQL query to determine the date of the last full database backup and also return the size and location of the backup file. My problem is that it won't return any data at all for ...
Fza's user avatar
  • 652
49 votes
8 answers

Writing select result to a csv file

We need to write the SELECT query results to a csv file. How can it be done using T-SQL in SQL Server 2008 r2? I know that it can be done in SSIS, but for some reasons, we don't have this option. I ...
Sky's user avatar
  • 3,674
39 votes
8 answers

Does SQL Server support GREATEST and LEAST, if not what is the common workaround?

Reviewing this question it seems like that's a lot of work that shouldn't be needed. They're trying to extend a range with a date. In other databases, you would just use greatest and least.. least(...
Evan Carroll's user avatar
  • 62.2k
34 votes
3 answers

Running total with count?

As the title suggests I need some help getting a running total in T-SQL. The problem is that the sum I need to do is the sum of a count: sum(count (distinct (customers))) Say if I ran the count ...
user avatar
31 votes
5 answers

List Jobs, Schedules, and Next Scheduled Run Datetimes

List Jobs, Schedules, and Next Scheduled Run Datetimes I'm trying to list Jobs, Schedules, and Next Scheduled Run Datetimes. However, I don't appear to be succeeding. sysjobactivity has ...
Kennah's user avatar
  • 445
29 votes
1 answer

What date/time literal formats are LANGUAGE and DATEFORMAT safe?

It is easy to demonstrate that many date/time formats other than the following two are vulnerable to misinterpretation due to SET LANGUAGE, SET DATEFORMAT, or a login's default language: yyyyMMdd ...
Aaron Bertrand's user avatar
26 votes
6 answers

Index performance on ON versus WHERE

I have two tables @T1 TABLE ( Id INT, Date DATETIME ) @T2 TABLE ( Id INT, Date DATETIME ) These tables have a non-clustered index on (Id, Date) And I join these tables SELECT * ...
Erik Bergstedt's user avatar
26 votes
8 answers

Trying to find the last time that a value has changed

I have a table that has an ID, a value, and a date. There are many IDs, Values, and dates in this table. Records are inserted into this table periodically. The ID will always stay the same but ...
SqlSandwiches's user avatar
25 votes
1 answer

What exactly does "No Join Predicate" mean in SQL Server?

MSDN "Missing Join Predicate Event Class" says it "indicates that a query is being executed that has no join predicate". But unfortunately it does not seem to be as easy as that. For example, very ...
Jānis's user avatar
  • 1,248

2 3 4 5 6