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
729
votes
15 answers

How to select all records from one table that do not exist in another table?

table1 (id, name) table2 (id, name) Query: SELECT name FROM table2 -- that are not in table1 already
z-boss
  • 17,111
  • 12
  • 49
  • 81
602
votes
18 answers

Insert multiple rows WITHOUT repeating the "INSERT INTO ..." part of the statement?

I know I've done this before years ago, but I can't remember the syntax, and I can't find it anywhere due to pulling up tons of help docs and articles about "bulk imports". Here's what I want to do, but the syntax is not exactly right... please,…
Timothy Khouri
  • 31,315
  • 21
  • 88
  • 128
601
votes
11 answers

OR is not supported with CASE Statement in SQL Server

The OR operator in the WHEN clause of a CASE statement is not supported. How can I do this? CASE ebv.db_no WHEN 22978 OR 23218 OR 23219 THEN 'WECS 9500' ELSE 'WECS 9520' END as wecs_system
Werner
  • 6,125
  • 4
  • 17
  • 11
595
votes
23 answers

Best approach to remove time part of datetime in SQL Server

Which method provides the best performance when removing the time portion from a datetime field in SQL Server? a) select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0) or b) select cast(convert(char(11), getdate(), 113) as datetime) The second method…
Stephen Perelson
  • 6,613
  • 3
  • 21
  • 15
578
votes
30 answers

Cannot truncate table because it is being referenced by a FOREIGN KEY constraint?

Using MSSQL2005, can I truncate a table with a foreign key constraint if I first truncate the child table (the table with the primary key of the FK relationship)? I know that I can either Use a DELETE without a where clause and then RESEED the…
ctrlShiftBryan
  • 27,092
  • 26
  • 73
  • 78
577
votes
8 answers

Selecting COUNT(*) with DISTINCT

In SQL Server 2005 I have a table cm_production that lists all the code that's been put into production. The table has a ticket_number, program_type, program_name and push_number along with some other columns. GOAL: Count all the DISTINCT program…
somacore
  • 6,294
  • 3
  • 24
  • 19
566
votes
13 answers

Is it possible to specify condition in Count()?

Is it possible to specify a condition in Count()? I would like to count only the rows that have, for example, "Manager" in the Position column. I want to do it in the count statement, not using WHERE; I'm asking about it because I need to count both…
agnieszka
  • 14,897
  • 30
  • 95
  • 113
552
votes
18 answers

Pad a string with leading zeros so it's 3 characters long in SQL Server 2008

I have a string that is up to 3 characters long when it's first created in SQL Server 2008 R2. I would like to pad it with leading zeros, so if its original value was '1' then the new value would be '001'. Or if its original value was '23' the new…
Sunil
  • 20,653
  • 28
  • 112
  • 197
541
votes
6 answers

What is the significance of 1/1/1753 in SQL Server?

Why 1753? What do they have against 1752? My great great great great great great great grandfather would be very offended.
Daniel
  • 6,758
  • 6
  • 31
  • 29
536
votes
12 answers

T-SQL: Selecting rows to delete via joins

Scenario: Let's say I have two tables, TableA and TableB. TableB's primary key is a single column (BId), and is a foreign key column in TableA. In my situation, I want to remove all rows in TableA that are linked with specific rows in TableB: Can I…
John
  • 17,163
  • 16
  • 65
  • 83
529
votes
46 answers

How do I split a delimited string so I can access individual items?

Using SQL Server, how do I split a string so I can access item x? Take a string "Hello John Smith". How can I split the string by space and access the item at index 1 which should return "John"?
GateKiller
  • 74,180
  • 73
  • 171
  • 204
514
votes
24 answers

SQL MAX of multiple columns?

How do you return 1 value per row of the max of several columns: TableName [Number, Date1, Date2, Date3, Cost] I need to return something like this: [Number, Most_Recent_Date, Cost] Query?
BenB
  • 10,300
  • 7
  • 32
  • 30
501
votes
9 answers

how can I Update top 100 records in sql server

I want to update the top 100 records in SQL Server. I have a table T1 with fields F1 and F2. T1 has 200 records. I want to update the F1 field in the top 100 records. How can I update based on TOP 100 in SQL Server?
Rajesh
  • 6,269
  • 5
  • 28
  • 23
501
votes
4 answers

What is the meaning of the prefix N in T-SQL statements and when should I use it?

I have seen prefix N in some insert T-SQL queries. Many people have used N before inserting the value in a table. I searched, but I was not able to understand what is the purpose of including the N before inserting any strings into the table. INSERT…
Kartik Patel
  • 9,303
  • 12
  • 33
  • 53
500
votes
14 answers

Replace a newline in TSQL

I would like to replace (or remove) a newline character in a TSQL-string. Any Ideas? The obvious REPLACE(@string, CHAR(13), '') just won't do it...
Peter
  • 47,963
  • 46
  • 132
  • 181