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
220
votes
9 answers

is it possible to select EXISTS directly as a bit?

I was wondering if it's possible to do something like this (which doesn't work): select cast( (exists(select * from theTable where theColumn like 'theValue%') as bit) Seems like it should be doable, but lots of things that should work in SQL don't…
jcollum
  • 43,623
  • 55
  • 191
  • 321
219
votes
12 answers

How do I remove the first characters of a specific column in a table?

In SQL, how can I remove the first 4 characters of values of a specific column in a table? Column name is Student Code and an example value is ABCD123Stu1231. I want to remove first 4 chars from my table for all records Please guide me
Shyju
  • 214,206
  • 104
  • 411
  • 497
218
votes
20 answers

Most efficient T-SQL way to pad a varchar on the left to a certain length?

As compared to say: REPLICATE(@padchar, @len - LEN(@str)) + @str
Cade Roux
  • 88,164
  • 40
  • 182
  • 265
216
votes
8 answers

Is SQL or even TSQL Turing Complete?

This came up at the office today. I have no plans of doing such a thing, but theoretically could you write a compiler in SQL? At first glance it appears to me to be turing complete, though extremely cumbersome for many classes of problems. If it…
Matthew Vines
  • 27,253
  • 7
  • 76
  • 97
216
votes
13 answers

How to ALTER multiple columns at once in SQL Server

I need to ALTER the data types of several columns in a table. For a single column, the following works fine: ALTER TABLE tblcommodityOHLC ALTER COLUMN CC_CommodityContractID NUMERIC(18,0) But how do I alter multiple columns in one statement?…
D.mahesh
  • 2,169
  • 2
  • 12
  • 3
216
votes
17 answers

Sql Server string to date conversion

I want to convert a string like this: '10/15/2008 10:06:32 PM' into the equivalent DATETIME value in Sql Server. In Oracle, I would say this: TO_DATE('10/15/2008 10:06:32 PM','MM/DD/YYYY HH:MI:SS AM') This question implies that I must parse the…
JosephStyons
  • 57,317
  • 63
  • 160
  • 234
215
votes
84 answers

Hidden Features of SQL Server

What are some hidden features of SQL Server? For example, undocumented system stored procedures, tricks to do things which are very useful but not documented enough? Answers Thanks to everybody for all the great answers! Stored…
Sklivvz
  • 30,601
  • 24
  • 116
  • 172
212
votes
9 answers

Multi-statement Table Valued Function vs Inline Table Valued Function

A few examples to show, just incase: Inline Table Valued CREATE FUNCTION MyNS.GetUnshippedOrders() RETURNS TABLE AS RETURN SELECT a.SaleId, a.CustomerID, b.Qty FROM Sales.Sales a INNER JOIN Sales.SaleDetail b ON a.SaleId = b.SaleId …
AndrewC
  • 6,680
  • 13
  • 43
  • 71
209
votes
7 answers

Alter column, add default constraint

I have a table and one of the columns is "Date" of type datetime. We decided to add a default constraint to that column Alter table TableName alter column dbo.TableName.Date default getutcdate() but this gives me error: Incorrect syntax near…
ram
  • 11,468
  • 16
  • 63
  • 89
207
votes
11 answers

How to get sp_executesql result into a variable?

I have a piece of dynamic SQL I need to execute, I then need to store the result into a variable. I know I can use sp_executesql but can't find clear examples around about how to do this.
JohnIdol
  • 48,899
  • 61
  • 158
  • 242
206
votes
19 answers

Better techniques for trimming leading zeros in SQL Server?

I've been using this for some time: SUBSTRING(str_col, PATINDEX('%[^0]%', str_col), LEN(str_col)) However recently, I've found a problem with columns with all "0" characters like '00000000' because it never finds a non-"0" character to match. An…
Cade Roux
  • 88,164
  • 40
  • 182
  • 265
206
votes
42 answers

How to generate a range of numbers between two numbers?

I have two numbers as input from the user, like for example 1000 and 1050. How do I generate the numbers between these two numbers, using a sql query, in seperate rows? I want this: 1000 1001 1002 1003 . . 1050
user3211705
  • 2,418
  • 3
  • 19
  • 36
204
votes
4 answers

Combining INSERT INTO and WITH/CTE

I have a very complex CTE and I would like to insert the result into a physical table. Is the following valid? INSERT INTO dbo.prf_BatchItemAdditionalAPartyNos ( BatchID, AccountNo, APartyNo, SourceRowID ) WITH tab ( --…
dcpartners
  • 5,176
  • 13
  • 50
  • 73
202
votes
5 answers

How to check existence of user-define table type in SQL Server 2008?

I have a user-defined table type. I want to check it's existence before editing in a patch using OBJECT_ID(name, type) function. What type from the enumeration should be passed for user-defined table types? N'U' like for user defined table doesn't…
abatishchev
  • 98,240
  • 88
  • 296
  • 433
200
votes
23 answers

Insert Update trigger how to determine if insert or update

I need to write an Insert, Update Trigger on table A which will delete all rows from table B whose one column (say Desc) has values like the value inserted/updated in the table A's column (say Col1). How would I go around writing it so that I can…
MSIL
  • 2,671
  • 5
  • 24
  • 25