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
303
votes
25 answers

Why do you create a View in a database?

When and Why does some one decide that they need to create a View in their database? Why not just run a normal stored procedure or select?
MedicineMan
  • 15,008
  • 32
  • 101
  • 146
303
votes
2 answers

How do I move a table into a schema in T-SQL

I want to move a table into a specific Schema using T-SQL? I am using SQL Server 2008.
Lukasz
  • 8,710
  • 12
  • 44
  • 72
301
votes
25 answers

How to get a date in YYYY-MM-DD format from a TSQL datetime field?

How do I retrieve a date from SQL Server in YYYY-MM-DD format? I need this to work with SQL Server 2000 and up. Is there a simple way to perform this in SQL Server or would it be easier to convert it programmatically after I retrieve the result…
Kinze
  • 3,780
  • 5
  • 21
  • 15
301
votes
21 answers

Is there a way to loop through a table variable in TSQL without using a cursor?

Let's say I have the following simple table variable: declare @databases table ( DatabaseID int, Name varchar(15), Server varchar(15) ) -- insert a bunch rows into @databases Is declaring and using a cursor my only…
Ray
  • 187,153
  • 97
  • 222
  • 204
301
votes
9 answers

How can I group by date time column without taking time into consideration

I have a bunch of product orders and I'm trying to group by the date and sum the quantity for that date. How can I group by the month/day/year without taking the time part into consideration? 3/8/2010 7:42:00 should be grouped with 3/8/2010 4:15:00
The Muffin Man
  • 19,585
  • 30
  • 119
  • 191
298
votes
32 answers

Generate class from database table

How can I generate a class from a SQL Server table object? I'm not talking about using some ORM. I just need to create the entities (simple class). Something like: public class Person { public string Name { get;set; } public…
Gui
  • 9,555
  • 10
  • 42
  • 54
297
votes
12 answers

How do I drop a foreign key constraint only if it exists in sql server?

I can drop a table if it exists using the following code but do not know how to do the same with a constraint: IF EXISTS(SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'TableName') AND type = (N'U')) DROP TABLE TableName go I also add the…
solrevdev
  • 8,863
  • 11
  • 41
  • 49
297
votes
10 answers

How can I escape square brackets in a LIKE clause?

I am trying to filter items with a stored procedure using like. The column is a varchar(15). The items I am trying to filter have square brackets in the name. For example: WC[R]S123456. If I do a LIKE 'WC[R]S123456' it will not return anything. I…
Travis
  • 3,389
  • 2
  • 19
  • 11
295
votes
4 answers

Is there a performance difference between CTE , Sub-Query, Temporary Table or Table Variable?

In this excellent SO question, differences between CTE and sub-queries were discussed. I would like to specifically ask: In what circumstance is each of the following more efficient/faster? CTE Sub-Query Temporary Table Table…
whytheq
  • 34,466
  • 65
  • 172
  • 267
292
votes
3 answers

Correct use of transactions in SQL Server

I have 2 commands and need both of them executed correctly or none of them executed. So I think I need a transaction, but I don't know how to use it correctly. What's the problem with the following script? BEGIN TRANSACTION [Tran1] INSERT INTO…
Saeid
  • 13,224
  • 32
  • 107
  • 173
283
votes
19 answers

What are the use cases for selecting CHAR over VARCHAR in SQL?

I realize that CHAR is recommended if all my values are fixed-width. But, so what? Why not just pick VARCHAR for all text fields just to be safe.
SkunkSpinner
  • 11,429
  • 7
  • 40
  • 53
283
votes
20 answers

How to update Identity Column in SQL Server?

I have SQL Server database and I want to change the identity column because it started with a big number 10010 and it's related with another table, now I have 200 records and I want to fix this issue before the records increases. What's the best…
Abdulsalam Elsharif
  • 4,773
  • 7
  • 32
  • 66
281
votes
2 answers

Check if a string contains a substring in SQL Server 2005, using a stored procedure

I've a string, @mainString = 'CATCH ME IF YOU CAN'. I want to check whether the word ME is inside @mainString. How do I check if a string has a specific substring in SQL?
NLV
  • 21,141
  • 40
  • 118
  • 183
280
votes
15 answers

Create a date from day month and year with T-SQL

I am trying to convert a date with individual parts such as 12, 1, 2007 into a datetime in SQL Server 2005. I have tried the following: CAST(DATEPART(year, DATE)+'-'+ DATEPART(month, DATE) +'-'+ DATEPART(day, DATE) AS DATETIME) but this results in…
Brandon
  • 6,832
  • 11
  • 38
  • 50
276
votes
20 answers

Need to list all triggers in SQL Server database with table name and table's schema

I need to list all triggers in SQL Server database with table name and table's schema. I'm almost there with this: SELECT trigger_name = name, trigger_owner = USER_NAME(uid),table_schema = , table_name = OBJECT_NAME(parent_obj), isupdate =…
Ronnie Overby
  • 45,287
  • 73
  • 267
  • 346