Transact-SQL

Transact-SQL (T-SQL) is Microsoft's and Sybase's proprietary extension to the SQL (Structured Query Language) used to interact with relational databases. T-SQL expands on the SQL standard to include procedural programming, local variables, various support functions for string processing, date processing, mathematics, etc. and changes to the DELETE and UPDATE statements.

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.

Stored procedures in SQL Server are executable server-side routines. The advantage of stored procedures is the ability to pass parameters.

Variables
Transact-SQL provides the following statements to declare and set local variables:,   and.

Flow control
Keywords for flow control in Transact-SQL include  and ,  ,  ,  ,   and  ,  ,  , and.

and  allow conditional execution. This batch statement will print "It is the weekend" if the current date is a weekend day, or "It is a weekday" if the current date is a weekday. (Note: This code assumes that Sunday is configured as the first day of the week in the  setting.)

and  mark a block of statements. If more than one statement is to be controlled by the conditional in the example above, we can use  and   like this:

will wait for a given amount of time, or until a particular time of day. The statement can be used for delays or to block execution until the set time.

is used to immediately return from a stored procedure or function.

ends the enclosing  loop, while   causes the next iteration of the loop to execute. An example of a  loop is given below.

Changes to DELETE and UPDATE statements
In Transact-SQL, both the  and   statements are enhanced to enable data from another table to be used in the operation, without needing a subquery:
 * accepts joined tables in the  clause, similarly to  .  When this is done, the name or alias of which table in the join is to be deleted from is placed between   and.
 * allows a  clause to be added.  The table to be updated can be either joined in the   clause and referenced by alias, or referenced only at the start of the statement as per standard SQL.

This example deletes all users who have been flagged in the user_flags table with the 'idle' flag.

BULK INSERT
is a Transact-SQL statement that implements a bulk data-loading process, inserting multiple rows into a table, reading data from an external sequential file. Use of  results in better performance than processes that issue individual   statements for each row to be added. Additional details are available in MSDN.

TRY CATCH
Beginning with SQL Server 2005, Microsoft introduced additional  logic to support exception type behaviour. This behaviour enables developers to simplify their code and leave out  checking after each SQL execution statement.