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
136
votes
7 answers

How to assign an exec result to a sql variable?

How do you assign the result of an exec call to a variable in SQL? I have a stored proc called up_GetBusinessDay, which returns a single date. Can you do something like this: exec @PreviousBusinessDay = dbo.up_GetBusinessDay @Date, -1
Prabhu
  • 12,995
  • 33
  • 127
  • 210
136
votes
15 answers

Dynamic Sorting within SQL Stored Procedures

This is an issue that I've spent hours researching in the past. It seems to me to be something that should have been addressed by modern RDBMS solutions but as yet I have not found anything that really addresses what I see to be an incredibly…
Sean Hanley
  • 5,677
  • 7
  • 42
  • 53
134
votes
3 answers

Is having an 'OR' in an INNER JOIN condition a bad idea?

In trying to improve the speed of an immensely slow query (several minutes on two tables with only ~50,000 rows each, on SQL Server 2008 if it matters), I narrowed down the problem to an OR in my inner join, as in: SELECT mt.ID, mt.ParentID,…
ladenedge
  • 13,197
  • 11
  • 60
  • 117
134
votes
11 answers

Avoid duplicates in INSERT INTO SELECT query in SQL Server

I have the following two tables: Table1 ---------- ID Name 1 A 2 B 3 C Table2 ---------- ID Name 1 Z I need to insert data from Table1 to Table2. I can use the following syntax: INSERT INTO Table2(Id, Name) SELECT Id, Name FROM…
Ashish Gupta
  • 14,869
  • 20
  • 75
  • 134
133
votes
13 answers

How do I delete from multiple tables using INNER JOIN in SQL server

In MySQL you can use the syntax DELETE t1,t2 FROM table1 AS t1 INNER JOIN table2 t2 ... INNER JOIN table3 t3 ... How do I do the same thing in SQL Server?
Byron Whitlock
  • 52,691
  • 28
  • 123
  • 168
133
votes
2 answers

Best way to work with transactions in MS SQL Server Management Studio

Let's say I have an SQL statement that's syntactically and semantically correct so it executes. In Management Studio (or any other query tool) how can I test SQL statements, and if I notice that they broke something, rollback (in a separate query?)
Niels Bosma
  • 11,758
  • 29
  • 89
  • 148
133
votes
10 answers

Which SQL query is faster? Filter on Join criteria or Where clause?

Compare these 2 queries. Is it faster to put the filter on the join criteria or in the WHERE clause. I have always felt that it is faster on the join criteria because it reduces the result set at the soonest possible moment, but I don't know for…
Jon Erickson
  • 112,242
  • 44
  • 136
  • 174
132
votes
7 answers

varbinary to string on SQL Server

How to convert a column value from varbinary(max) to varchar in human-readable form?
Bilgin Kılıç
  • 8,707
  • 14
  • 41
  • 67
132
votes
12 answers

Is the NOLOCK (Sql Server hint) bad practice?

I'm in the business of making website and applications that are not mission critical -> eg. banking software, space flight, intensive care monitoring application, etc. You get the idea. So, with that massive disclaimer, is it bad using the NOLOCK…
Pure.Krome
  • 84,693
  • 113
  • 396
  • 647
131
votes
15 answers

SQL Server 2008: How to query all databases sizes?

I have MS SQL 2008 R2, 500 databases. What is the most efficient, easiest and 'modern' way to query all databases sizes. The output should have columns: DatabaseName DataFilesSize LogFilesSize
Alex Aza
  • 76,499
  • 26
  • 155
  • 134
131
votes
10 answers

Generate MD5 hash string with T-SQL

Is there a way to generate MD5 Hash string of type varchar(32) without using fn_varbintohexstr SUBSTRING(master.dbo.fn_varbintohexstr(HashBytes('MD5', 'email@dot.com')), 3, 32) So it could be used inside a view with SCHEMABINDING
Grief Coder
  • 6,508
  • 9
  • 38
  • 51
131
votes
7 answers

SQL Server: Examples of PIVOTing String data

Trying to find some simple SQL Server PIVOT examples. Most of the examples that I have found involve counting or summing up numbers. I just want to pivot some string data. For example, I have a query returning the following. Action1 VIEW …
Tim Cochran
  • 1,834
  • 2
  • 15
  • 13
130
votes
4 answers

Is there StartsWith or Contains in t sql with variables?

I am trying to detect if the server is running Express Edition. I have the following t sql. DECLARE @edition varchar(50); set @edition = cast((select SERVERPROPERTY ('edition')) as varchar) print @edition In my instance, @edition = Express…
Valamas
  • 24,169
  • 25
  • 107
  • 177
130
votes
16 answers

How do you check what version of SQL Server for a database using TSQL?

Is there a system stored procedure to get the version #?
Ray
  • 187,153
  • 97
  • 222
  • 204
130
votes
15 answers

SQL Server: Get data for only the past year

I am writing a query in which I have to get the data for only the last year. What is the best way to do this? SELECT ... FROM ... WHERE date > '8/27/2007 12:00:00 AM'
Josh Mein
  • 28,107
  • 15
  • 76
  • 87