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
338
votes
20 answers

Counting DISTINCT over multiple columns

Is there a better way of doing a query like this: SELECT COUNT(*) FROM (SELECT DISTINCT DocumentId, DocumentSessionId FROM DocumentOutputItems) AS internalQuery I need to count the number of distinct items from this table but the distinct is…
Novitzky
  • 4,756
  • 3
  • 23
  • 27
330
votes
7 answers

SELECT DISTINCT on one column

Using SQL Server, I have... ID SKU PRODUCT ======================= 1 FOO-23 Orange 2 BAR-23 Orange 3 FOO-24 Apple 4 FOO-25 Orange I want 1 FOO-23 Orange 3 FOO-24 Apple This query isn't getting me there. How can I SELECT…
mmcglynn
  • 7,668
  • 16
  • 52
  • 76
329
votes
18 answers

What is a stored procedure?

What is a "stored procedure" and how do they work? What is the make-up of a stored procedure (things each must have to be a stored procedure)?
George Stocker
  • 57,289
  • 29
  • 176
  • 237
327
votes
18 answers

Turning a Comma Separated string into individual rows

I have a SQL Table like this: SomeID OtherID Data abcdef-..... cdef123-... 18,20,22 abcdef-..... 4554a24-... 17,19 987654-..... 12324a2-... 13,19,20 Is there a query where I can perform a query like SELECT OtherID, SplitData WHERE…
Michael Stum
  • 177,530
  • 117
  • 400
  • 535
327
votes
7 answers

SQL Server SELECT INTO @variable?

I have the following code in one of my Sql (2008) Stored Procs which executes perfectly fine: CREATE PROCEDURE [dbo].[Item_AddItem] @CustomerId uniqueidentifier, @Description nvarchar(100), @Type int, @Username…
bleepzter
  • 9,607
  • 11
  • 41
  • 64
327
votes
5 answers

SET versus SELECT when assigning variables?

What are the differences between the SET and SELECT statements when assigning variables in T-SQL?
juur
  • 5,633
  • 10
  • 32
  • 36
325
votes
6 answers

How to check if a database exists in SQL Server?

What is the ideal way to check if a database exists on a SQL Server using TSQL? It seems multiple approaches to implement this.
Ray
  • 187,153
  • 97
  • 222
  • 204
322
votes
14 answers

How can I truncate a datetime in SQL Server?

What's the best way to truncate a datetime value (as to remove hours minutes and seconds) in SQL Server? For example: declare @SomeDate datetime = '2009-05-28 16:30:22' select trunc_date(@SomeDate) ----------------------- 2009-05-28 00:00:00.000
Julio César
  • 12,790
  • 10
  • 38
  • 45
318
votes
10 answers

Syntax of for-loop in SQL Server

What is the syntax of a for loop in TSQL?
Macho
  • 3,307
  • 3
  • 16
  • 6
316
votes
3 answers

T-SQL: Deleting all duplicate rows but keeping one

I have a table with a very large amount of rows. Duplicates are not allowed but due to a problem with how the rows were created I know there are some duplicates in this table. I need to eliminate the extra rows from the perspective of the key…
nuit9
  • 3,181
  • 3
  • 16
  • 5
316
votes
22 answers

Drop all the tables, stored procedures, triggers, constraints and all the dependencies in one sql statement

Is there any way in which I can clean a database in SQl Server 2005 by dropping all the tables and deleting stored procedures, triggers, constraints and all the dependencies in one SQL statement? REASON FOR REQUEST: I want to have a DB script for…
renegadeMind
  • 4,073
  • 5
  • 29
  • 37
315
votes
12 answers

NULL values inside NOT IN clause

This issue came up when I got different records counts for what I thought were identical queries one using a not in where constraint and the other a left join. The table in the not in constraint had one null value (bad data) which caused that query…
Jamie Ide
  • 48,427
  • 16
  • 81
  • 117
311
votes
14 answers

Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)

I have a development database that re-deploy frequently from a Visual Studio Database project (via a TFS Auto Build). Sometimes when I run my build I get this error: ALTER DATABASE failed because a lock could not be placed on database 'MyDB'. Try…
Vaccano
  • 78,325
  • 149
  • 468
  • 850
308
votes
18 answers

Find a value anywhere in a database

Given a number, how do I discover in what table and column it could be found within? I don't care if it's fast, it just needs to work.
Allain Lalonde
  • 91,574
  • 70
  • 187
  • 238
306
votes
9 answers

SQL RANK() versus ROW_NUMBER()

I'm confused about the differences between these. Running the following SQL gets me two idential result sets. Can someone please explain the differences? SELECT ID, [Description], RANK() OVER(PARTITION BY StyleID ORDER BY ID) as 'Rank' …
dotNET Hobbiest
  • 3,145
  • 3
  • 14
  • 6