Questions tagged [tsql]

T-SQL (Transact Structured Query Language) is the extension of SQL functionality supported by Sybase ASE and Microsoft SQL Server. Do not use this tag for MySQL, PostgreSql, Oracle(Pl/SQL) related queries. Please note that SQL code which is being written using LINQ will not also be the part of this tag. This tag specifically created for advanced SQL programming using Microsoft SQL Server.

When to use this tag:
Use this tag for SQL questions for or .
Do not use this tag for questions about other database vendors, since T-SQL is only used by these two products.

Questions about T-SQL should be tagged appropriately with either or , and include the version as well.

How to write a good T-SQL question:
There are five ingredients to this recipe:

  1. Provide a clear textual explanation of the problem at hand.
  2. Provide proper sample data, as DDL (Create table statement(s)) and DML (Insert statement(s)). The sample data should be enough to demonstrate the problem, including edge cases. Usually, a few relevant rows are enough. (You can use SQLFiddle as an online tool to generate DDL and DML statements for you from tabular data using their Text To DDL button.)
  3. Provide expected output for the sample data you've posted.
  4. Show your attempts to solve the problem.
  5. Tag properly. In addition to , also provide the product tag (either or ), and the lowest version you need the solution for.
    (i.e. If you're developing on SQL Server 2016, but the production server is SQL Server 2012, Use .)

Do not include images of data or code!

A link to an online SQL test environment such as SQL Fiddle or DB Fiddle might help, but it is not a replacement for having all the data inside the question.
For more information, Read Why should I provide an MCVE for what seems to me to be a very simple SQL query? and Help me write this query in SQL

General Information

T-SQL (Transact Structured Query Language) is the extension of SQL functionality supported by Sybase ASE and Microsoft SQL Server. They share the name because Microsoft SQL Server was based on Sybase ASE code in a partnership that later dissolved. While the name is shared, functionality is not necessarily identical. With the introduction of Microsoft SQL Server 2005, the SQL Server code was completely re-written to provide integration with .Net.

Transact-SQL is central to using Microsoft SQL Server. All applications that communicate with an instance of SQL Server do so by sending Transact-SQL statements to the server, regardless of the user interface of the application.

It adheres to nearly all SQL standards, yet supplies an additional set of functionality not a part of the SQL standard, with concepts such as stored procedure, triggers , common table expressions, index hints, and the ability to SELECT scalars.

More information can be found in SQL Server Transact-SQL Reference and Sybase Transact-SQL User's Guide.

Free online T-SQL courses

72684 questions
274
votes
20 answers

How do you return the column names of a table?

How would I return the column names of a table using SQL Server 2008? i.e. a table contains these columns- id, name, address, country and I want to return these as data.
Belliez
  • 5,356
  • 12
  • 54
  • 62
274
votes
38 answers

Function to Calculate Median in SQL Server

According to MSDN, Median is not available as an aggregate function in Transact-SQL. However, I would like to find out whether it is possible to create this functionality (using the Create Aggregate function, user defined function, or some other…
Yaakov Ellis
  • 40,752
  • 27
  • 129
  • 174
269
votes
10 answers

Why use a READ UNCOMMITTED isolation level?

In plain English, what are the disadvantages and advantages of using SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED in a query for .NET applications and reporting services applications?
Kip Real
  • 3,319
  • 4
  • 21
  • 28
268
votes
12 answers

Check if a row exists, otherwise insert

I need to write a T-SQL stored procedure that updates a row in a table. If the row doesn't exist, insert it. All this steps wrapped by a transaction. This is for a booking system, so it must be atomic and reliable. It must return true if the…
Robert
268
votes
10 answers

How to write a foreach in SQL Server?

I am trying to achieve something along the lines of a for-each, where I would like to take the Ids of a returned select statement and use each of them. DECLARE @i int DECLARE @PractitionerId int DECLARE @numrows int DECLARE @Practitioner TABLE ( …
Pomster
  • 14,567
  • 55
  • 128
  • 204
267
votes
3 answers

I want to use CASE statement to update some records in sql server 2005

UPDATE dbo.TestStudents SET LASTNAME = ( CASE WHEN (LASTNAME = 'AAA') THEN 'BBB' WHEN (LASTNAME = 'CCC') THEN 'DDD' WHEN (LASTNAME = 'EEE') THEN 'FFF' ELSE (LASTNAME) END ) The statement work for the purpose but the else condition scan…
amsbarry
  • 2,773
  • 2
  • 13
  • 6
264
votes
13 answers

When should I use semicolons in SQL Server?

While checking some code on the web and scripts generated by SQL Server Management Studio I have noticed that some statements are ended with a semicolon. So when should I use it?
Anwar Pinto
  • 2,785
  • 2
  • 17
  • 13
260
votes
11 answers

How do I create a foreign key in SQL Server?

I have never "hand-coded" object creation code for SQL Server and foreign key decleration is seemingly different between SQL Server and Postgres. Here is my sql so far: drop table exams; drop table question_bank; drop table anwser_bank; create…
mmattax
  • 27,172
  • 41
  • 116
  • 149
260
votes
9 answers

When to use Common Table Expression (CTE)

I have begun reading about Common Table Expression and cannot think of a use case where I would need to use them. They would seem to be redundant as the same can be done with derived tables. Is there something I am missing or not understanding well?…
imak
  • 6,489
  • 7
  • 50
  • 73
257
votes
6 answers

How do I flush the PRINT buffer in TSQL?

I have a very long-running stored procedure in SQL Server 2005 that I'm trying to debug, and I'm using the 'print' command to do it. The problem is, I'm only getting the messages back from SQL Server at the very end of my sproc - I'd like to be able…
Erik Forbes
  • 35,357
  • 27
  • 98
  • 122
256
votes
4 answers

How to wait for 2 seconds?

How does one cause a delay in execution for a specified number of seconds? This doesn't do it: WAITFOR DELAY '00:02'; What is the correct format?
Chad
  • 23,658
  • 51
  • 191
  • 321
256
votes
18 answers

T-SQL CASE Clause: How to specify WHEN NULL

I wrote a T-SQL Statement similar like this (the original one looks different but I want to give an easy example here): SELECT first_name + CASE last_name WHEN null THEN 'Max' ELSE 'Peter' END AS Name FROM dbo.person This Statement does not…
meni
  • 2,563
  • 2
  • 14
  • 4
254
votes
12 answers

Extracting hours from a DateTime (SQL Server 2005)

I can extract the month and day by using Day(Date()), Month(Date()). I can't extract hours, with HOUR(Date()). I get the following error. 'HOUR' is not a recognized built-in function name. How can I extract hours?
Efe
254
votes
9 answers

SQL Server dynamic PIVOT query?

I've been tasked with coming up with a means of translating the following data: date category amount 1/1/2012 ABC 1000.00 2/1/2012 DEF 500.00 2/1/2012 GHI 800.00 2/10/2012 DEF …
Sean Cunningham
  • 3,006
  • 5
  • 24
  • 35
253
votes
11 answers

How do you view ALL text from an ntext or nvarchar(max) in SSMS?

How do you view ALL text from an NTEXT or NVARCHAR(max) in SQL Server Management Studio? By default, it only seems to return the first few hundred characters (255?) but sometimes I just want a quick way of viewing the whole field, without having to…
NickG
  • 9,315
  • 16
  • 75
  • 115