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
237
votes
12 answers

Getting the minimum of two values in SQL

I have two variables, one is called PaidThisMonth, and the other is called OwedPast. They are both results of some subqueries in SQL. How can I select the smaller of the two and return it as a value titled PaidForPast? The MIN function works on…
Malfist
  • 31,179
  • 61
  • 182
  • 269
235
votes
6 answers

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS

My query is as follows, and contains a subquery within it: select count(distinct dNum) from myDB.dbo.AQ where A_ID in (SELECT DISTINCT TOP (0.1) PERCENT A_ID, COUNT(DISTINCT dNum) AS ud FROM myDB.dbo.AQ WHERE M > 1…
rockit
  • 3,708
  • 7
  • 26
  • 36
234
votes
8 answers

How do I drop a foreign key in SQL Server?

I have created a foreign key (in SQL Server) by: alter table company add CountryID varchar(3); alter table company add constraint Company_CountryID_FK foreign key(CountryID) references Country; I then run this query: alter table company drop…
mmattax
  • 27,172
  • 41
  • 116
  • 149
234
votes
3 answers

Creating an index on a table variable

Can you create an index on a table variable in SQL Server 2000? i.e. DECLARE @TEMPTABLE TABLE ( [ID] [int] NOT NULL PRIMARY KEY ,[Name] [nvarchar] (255) COLLATE DATABASE_DEFAULT NULL ) Can I create an index on Name?
GordyII
  • 7,067
  • 16
  • 51
  • 69
232
votes
16 answers

How to drop SQL default constraint without knowing its name?

In Microsoft SQL Server, I know the query to check if a default constraint exists for a column and drop a default constraint is: IF EXISTS(SELECT * FROM sysconstraints WHERE id=OBJECT_ID('SomeTable') AND COL_NAME(id,colid)='ColName' AND…
Robo
  • 4,588
  • 7
  • 40
  • 48
231
votes
18 answers

How can I group time by hour or by 10 minutes?

Like when I do SELECT [Date] FROM [FRIIB].[dbo].[ArchiveAnalog] GROUP BY [Date] How can I specify the group period? I'm using MS SQL 2008. I've tried this, both with % 10 and / 10. SELECT MIN([Date]) AS RecT, AVG(Value) FROM…
cnd
  • 32,616
  • 62
  • 183
  • 313
231
votes
2 answers

Keeping it simple and how to do multiple CTE in a query

I have this simple T-SQL query, it emits a bunch of columns from a table and also joins information from other related tables. My data model is simple. I have a scheduled event, with participants. I need to know how many participants participate in…
John Leidegren
  • 59,920
  • 20
  • 131
  • 152
229
votes
4 answers

How do I escape a percentage sign in T-SQL?

This question also has the answer, but it mentions DB2 specifically. How do I search for a string using LIKE that already has a percent % symbol in it? The LIKE operator uses % symbols to signify wildcards.
Jedidja
  • 16,610
  • 17
  • 73
  • 112
229
votes
6 answers

SQL JOIN vs IN performance?

I have a case where using a JOIN or an IN will give me the correct results... Which typically has better performance and why? How much does it depend on what database server you are running? (FYI I am using MSSQL)
DigitalZebra
  • 39,494
  • 39
  • 114
  • 146
228
votes
17 answers

How do you truncate all tables in a database using TSQL?

I have a test environment for a database that I want to reload with new data at the start of a testing cycle. I am not interested in rebuilding the entire database- just simply "re-setting" the data. What is the best way to remove all the data from…
Ray
  • 187,153
  • 97
  • 222
  • 204
226
votes
7 answers

Can you create nested WITH clauses for Common Table Expressions?

WITH y AS ( WITH x AS ( SELECT * FROM MyTable ) SELECT * FROM x ) SELECT * FROM y Does something like this work? I tried it earlier but I couldn't get it to work.
Joe Phillips
  • 49,743
  • 32
  • 103
  • 159
225
votes
21 answers

Truncate (not round) decimal places in SQL Server

I'm trying to determine the best way to truncate or drop extra decimal places in SQL without rounding. For example: declare @value decimal(18,2) set @value = 123.456 This will automatically round @value to be 123.46, which is good in most cases. …
Ryan Eastabrook
  • 4,085
  • 5
  • 30
  • 35
225
votes
19 answers

In SQL, how can you "group by" in ranges?

Suppose I have a table with a numeric column (lets call it "score"). I'd like to generate a table of counts, that shows how many times scores appeared in each range. For example: score range | number of…
Hugh
  • 2,519
  • 2
  • 17
  • 11
223
votes
10 answers

A table name as a variable

I am trying to execute this query: declare @tablename varchar(50) set @tablename = 'test' select * from @tablename This produces the following error: Msg 1087, Level 16, State 1, Line 5 Must declare the table variable "@tablename". What's the…
SoftwareGeek
  • 15,234
  • 19
  • 61
  • 78
223
votes
42 answers

How to calculate age (in years) based on Date of Birth and getDate()

I have a table listing people along with their date of birth (currently a nvarchar(25)) How can I convert that to a date, and then calculate their age in years? My data looks as follows ID Name DOB 1 John 1992-01-09 00:00:00 2 Sally …
Jimmy
  • 16,123
  • 39
  • 133
  • 213