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
200
votes
14 answers

Count number of records returned by group by

How do I count the number of records returned by a group by query, For eg: select count(*) from temptable group by column_1, column_2, column_3, column_4 Gives me, 1 1 2 I need to count the above records to get 1+1+1 = 3.
Chris
  • 2,923
  • 6
  • 26
  • 25
199
votes
6 answers

How can I use optional parameters in a T-SQL stored procedure?

I am creating a stored procedure to do a search through a table. I have many different search fields, all of which are optional. Is there a way to create a stored procedure that will handle this? Let's say I have a table with four fields: ID,…
Corey Burnett
  • 7,312
  • 10
  • 56
  • 93
199
votes
30 answers

Getting only Month and Year from SQL DATE

I need to access only Month.Year from Date field in SQL Server.
user160820
  • 14,866
  • 22
  • 67
  • 94
197
votes
24 answers

Count work days between two dates

How can I calculate the number of work days between two dates in SQL Server? Monday to Friday and it must be T-SQL.
Ovidiu Pacurar
  • 8,173
  • 2
  • 30
  • 36
196
votes
11 answers

CROSS JOIN vs INNER JOIN in SQL

What is the difference between CROSS JOIN and INNER JOIN? CROSS JOIN: SELECT Movies.CustomerID, Movies.Movie, Customers.Age, Customers.Gender, Customers.[Education Level], Customers.[Internet Connection], Customers.[Marital Status],…
PriceCheaperton
  • 5,071
  • 17
  • 52
  • 94
191
votes
11 answers

Pass Array Parameter in SqlCommand

I am trying to pass array parameter to SQL commnd in C# like below, but it does not work. Does anyone meet it before? string sqlCommand = "SELECT * from TableA WHERE Age IN (@Age)"; SqlConnection sqlCon = new…
Yongwei Xing
  • 12,983
  • 24
  • 70
  • 90
190
votes
15 answers

Calculate a Running Total in SQL Server

Imagine the following table (called TestTable): id somedate somevalue -- -------- --------- 45 01/Jan/09 3 23 08/Jan/09 5 12 02/Feb/09 0 77 14/Feb/09 7 39 20/Feb/09 34 33 02/Mar/09 6 I would like a…
codeulike
  • 22,514
  • 29
  • 120
  • 167
188
votes
27 answers

T-SQL split string

I have a SQL Server 2008 R2 column containing a string which I need to split by a comma. I have seen many answers on StackOverflow but none of them works in R2. I have made sure I have select permissions on any split function examples. Any help…
Lee Grindon
  • 2,117
  • 2
  • 16
  • 8
187
votes
10 answers

Difference between CTE and SubQuery?

From this post How to use ROW_NUMBER in the following procedure? There are two versions of answers where one uses a sub-query and the other uses a CTE to solve the same problem. Now then, what is the advantage of using a CTE (Common Table…
dance2die
  • 35,807
  • 39
  • 131
  • 194
185
votes
8 answers

Imply bit with constant 1 or 0 in SQL Server

Is it possible to express 1 or 0 as a bit when used as a field value in a select statement? e.g. In this case statement (which is part of a select statement) ICourseBased is of type int. case when FC.CourseId is not null then 1 else 0 end as…
Damien McGivern
  • 3,954
  • 3
  • 27
  • 21
184
votes
3 answers

GRANT EXECUTE to all stored procedures

Does the following command effectively give the user, "MyUser," permission to execute ALL stored procedures in the database? GRANT EXECUTE TO [MyDomain\MyUser]
Chad
  • 23,658
  • 51
  • 191
  • 321
183
votes
4 answers

T-SQL - function with default parameters

I have this script: CREATE FUNCTION dbo.CheckIfSFExists(@param1 INT, @param2 BIT = 1 ) RETURNS BIT AS BEGIN IF EXISTS ( bla bla bla ) RETURN 1; RETURN 0; END GO I want to use it in a procedure in this way: IF dbo.CheckIfSFExists(…
nirmus
  • 4,913
  • 9
  • 31
  • 50
183
votes
23 answers

Remove the last character in a string in T-SQL?

How do I remove the last character in a string in T-SQL? For example: 'TEST STRING' to return: 'TEST STRIN'
Daveed
  • 1,851
  • 2
  • 12
  • 5
182
votes
10 answers

SQL "between" not inclusive

I have a query like this: SELECT * FROM Cases WHERE created_at BETWEEN '2013-05-01' AND '2013-05-01' But this gives no results even though there is data on the 1st. created_at looks like 2013-05-01 22:25:19, I suspect it has to do with the time?…
JBurace
  • 5,123
  • 17
  • 51
  • 76
179
votes
24 answers

How do you count the number of occurrences of a certain substring in a SQL varchar?

I have a column that has values formatted like a,b,c,d. Is there a way to count the number of commas in that value in T-SQL?
Orion Adrian
  • 19,053
  • 13
  • 51
  • 67