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
178
votes
13 answers

How to identify all stored procedures referring a particular table

I created a table on development environment for testing purpose and there are few sp's which are refreing this table. Now I have have to drop this table as well as identify all sp's which are referring this table. I am facing difficulty to find…
DJay
  • 2,457
  • 2
  • 18
  • 22
178
votes
4 answers

IndexOf function in T-SQL

Given an email address column, I need to find the position of the @ sign for substringing. What is the indexof function, for strings in T-SQL? Looking for something that returns the position of a substring within a string. in C# var s =…
DevelopingChris
  • 39,797
  • 30
  • 87
  • 118
178
votes
7 answers

How do I make a composite key with SQL Server Management Studio?

How do I make a composite key with SQL Server Management Studio? I want two INT columns to form the identity (unique) for a table
mrblah
  • 99,669
  • 140
  • 310
  • 420
178
votes
6 answers

SQL Server equivalent to MySQL enum data type?

Does SQL Server 2008 have a a data-type like MySQL's enum?
Patrick
  • 5,442
  • 9
  • 53
  • 104
178
votes
10 answers

TSQL Pivot without aggregate function

I have a table like…
ctrlShiftBryan
  • 27,092
  • 26
  • 73
  • 78
175
votes
4 answers

How do I escape double quotes in attributes in an XML String in T-SQL?

Pretty simple question - I have an attribute that I would like to have double quotes in. How do I escape them? I've tried \" "" \\" And I've made the @xml variable both xml type and varchar(max) for all of them. declare @xml xml --(or…
Tom Ritter
  • 99,986
  • 30
  • 138
  • 174
175
votes
6 answers

Compare DATETIME and DATE ignoring time portion

I have two tables where column [date] is type of DATETIME2(0). I have to compare two records only by theirs Date parts (day+month+year), discarding Time parts (hours+minutes+seconds). How can I do that?
abatishchev
  • 98,240
  • 88
  • 296
  • 433
172
votes
5 answers

What does 'COLLATE SQL_Latin1_General_CP1_CI_AS' do?

I have an SQL query to create the database in SQLServer as given below: create database yourdb on ( name = 'yourdb_dat', filename = 'c:\program files\microsoft sql server\mssql.1\mssql\data\yourdbdat.mdf', size = 25mb, maxsize = 1500mb, …
Thunder
  • 10,366
  • 25
  • 84
  • 114
172
votes
16 answers

Define variable to use with IN operator (T-SQL)

I have a Transact-SQL query that uses the IN operator. Something like this: select * from myTable where myColumn in (1,2,3,4) Is there a way to define a variable to hold the entire list "(1,2,3,4)"? How should I define it? declare @myList {data…
Marcos Crispino
  • 8,018
  • 5
  • 41
  • 59
169
votes
8 answers

WITH CHECK ADD CONSTRAINT followed by CHECK CONSTRAINT vs. ADD CONSTRAINT

I'm looking at the AdventureWorks sample database for SQL Server 2008, and I see in their creation scripts that they tend to use the following: ALTER TABLE [Production].[ProductCostHistory] WITH CHECK ADD CONSTRAINT…
Wayne Molina
  • 19,158
  • 26
  • 98
  • 163
168
votes
12 answers

SQL "select where not in subquery" returns no results

Disclaimer: I have figured out the problem (I think), but I wanted to add this issue to Stack Overflow since I couldn't (easily) find it anywhere. Also, someone might have a better answer than I do. I have a database where one table "Common" is…
Jeremy Stein
  • 19,171
  • 16
  • 68
  • 83
164
votes
4 answers

SQL Server Output Clause into a scalar variable

Is there any "simple" way to do this or I need to pass by a table variable with the "OUTPUT ... INTO" syntax? DECLARE @someInt int INSERT INTO MyTable2(AIntColumn) OUTPUT @SomeInt = Inserted.AIntColumn VALUES(12)
Benoittr
  • 4,091
  • 4
  • 27
  • 38
164
votes
9 answers

How to report an error from a SQL Server user-defined function

I'm writing a user-defined function in SQL Server 2008. I know that functions cannot raise errors in the usual way - if you try to include the RAISERROR statement SQL returns: Msg 443, Level 16, State 14, Procedure ..., Line ... Invalid use of a…
EMP
  • 59,148
  • 53
  • 164
  • 220
163
votes
7 answers

SQL Server : Columns to Rows

Looking for elegant (or any) solution to convert columns to rows. Here is an example: I have a table with the following schema: [ID] [EntityID] [Indicator1] [Indicator2] [Indicator3] ... [Indicator150] Here is what I want to get as the result: [ID]…
Sergei
  • 1,745
  • 2
  • 11
  • 6
163
votes
5 answers

Reference alias (calculated in SELECT) in WHERE clause

SELECT (InvoiceTotal - PaymentTotal - CreditTotal) AS BalanceDue FROM Invoices WHERE BalanceDue > 0 --error The calculated value BalanceDue that is set as a variable in the list of selected columns cannot be used in the WHERE clause. Is there a way…
Nicholas Petersen
  • 9,104
  • 7
  • 59
  • 69