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
1120
votes
15 answers

When should I use CROSS APPLY over INNER JOIN?

What is the main purpose of using CROSS APPLY? I have read (vaguely, through posts on the Internet) that cross apply can be more efficient when selecting over large data sets if you are partitioning. (Paging comes to mind) I also know that CROSS…
Jeff Meatball Yang
  • 37,839
  • 27
  • 91
  • 125
1105
votes
47 answers

Exclude a column using SELECT * [except columnA] FROM tableA?

We all know that to select all columns from a table, we can use SELECT * FROM tableA Is there a way to exclude column(s) from a table without specifying all the columns? SELECT * [except columnA] FROM tableA The only way that I know is to manually…
uuɐɯǝʃǝs
1063
votes
20 answers

How do I get list of all tables in a database using TSQL?

What is the best way to get the names of all of the tables in a specific database on SQL Server?
Ray
  • 187,153
  • 97
  • 222
  • 204
1005
votes
23 answers

How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?
odiseh
  • 25,407
  • 33
  • 108
  • 151
982
votes
19 answers

Function vs. Stored Procedure in SQL Server

When should I use a function rather than a stored procedure in SQL, and vice versa? What is the purpose of each?
Tarik
  • 79,711
  • 83
  • 236
  • 349
967
votes
31 answers

How can I list all foreign keys referencing a given table in SQL Server?

I need to remove a highly referenced table in a SQL Server database. How can I get a list of all the foreign key constraints I will need to remove in order to drop the table? (SQL answers preferable over clicking about in the GUI of the management…
chillitom
  • 24,888
  • 17
  • 83
  • 118
961
votes
13 answers

Update a table using JOIN in SQL Server?

I want to update a column in a table making a join on other table e.g.: UPDATE table1 a INNER JOIN table2 b ON a.commonfield = b.[common field] SET a.CalculatedColumn= b.[Calculated Column] WHERE b.[common field]= a.commonfield AND a.BatchNO…
Manjot
  • 11,166
  • 9
  • 38
  • 49
934
votes
10 answers

How to Join to first row

I'll use a concrete, but hypothetical, example. Each Order normally has only one line item: Orders: OrderGUID OrderNumber ========= ============ {FFB2...} STL-7442-1 {3EC6...} MPT-9931-8A LineItems: LineItemGUID Order ID Quantity …
Ian Boyd
  • 246,734
  • 253
  • 869
  • 1,219
920
votes
17 answers

How can foreign key constraints be temporarily disabled using T-SQL?

Are disabling and enabling foreign key constraints supported in SQL Server? Or is my only option to drop and then re-create the constraints?
Ray
  • 187,153
  • 97
  • 222
  • 204
886
votes
14 answers

Should I use != or <> for not equal in T-SQL?

I have seen SQL that uses both != and <> for not equal. What is the preferred syntax and why? I like !=, because <> reminds me of Visual Basic.
Bob The Janitor
  • 20,292
  • 10
  • 49
  • 72
880
votes
17 answers

DateTime2 vs DateTime in SQL Server

Which one: datetime datetime2 is the recommended way to store date and time in SQL Server 2008+? I'm aware of differences in precision (and storage space probably), but ignoring those for now, is there a best practice document on when to use…
Mikeon
  • 10,499
  • 6
  • 27
  • 31
862
votes
15 answers

How to drop a table if it exists?

The table name is Scores. Is it correct to do the following? IF EXISTS(SELECT * FROM dbo.Scores) DROP TABLE dbo.Scores
tmaster
  • 9,345
  • 6
  • 24
  • 27
800
votes
13 answers

SQL update query using joins

I have to update a field with a value which is returned by a join of 3 tables. Example: select im.itemid ,im.sku as iSku ,gm.SKU as GSKU ,mm.ManufacturerId as ManuId ,mm.ManufacturerName ,im.mf_item_number …
Shyju
  • 214,206
  • 104
  • 411
  • 497
771
votes
15 answers

How do I create a unique constraint that also allows nulls?

I want to have a unique constraint on a column which I am going to populate with GUIDs. However, my data contains null values for this columns. How do I create the constraint that allows multiple null values? Here's an example scenario. Consider…
Stuart
  • 11,775
  • 6
  • 33
  • 31
753
votes
19 answers

Get top 1 row of each group

I have a table which I want to get the latest entry for each group. Here's the table: DocumentStatusLogs Table |ID| DocumentID | Status | DateCreated | | 2| 1 | S1 | 7/29/2011 | | 3| 1 | S2 | 7/30/2011 | | 6| 1 …
kazinix
  • 28,987
  • 33
  • 107
  • 157