Transact-SQL (T-SQL) is Microsoft's and Sybase's proprietary extension to SQL. SQL, often expanded to Structured Query Language, is a standardized computer language that was originally developed by IBM for querying, altering and defining relational databases, using declarative statements. 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. These additional features make Transact-SQL Turing complete.
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.
Transact-SQL provides the following statements to declare and set local variables:
DECLARE @var1 nvarchar(30) SET @var1 = 'Some Name' SELECT @var1 = Name FROM Sales.Store WHERE CustomerID = 1000
Flow control 
Keywords for flow control in Transact-SQL include
ELSE 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 @@DATEFIRST setting.)
IF DATEPART(dw, GETDATE()) = 7 OR DATEPART(dw, GETDATE()) = 1 PRINT 'It is the weekend.' ELSE PRINT 'It is a weekday.'
END mark a block of statements. If more than one statement is to be controlled by the conditional in the example above, we can use BEGIN and END like this:
IF DATEPART(dw, GETDATE()) = 7 OR DATEPART(dw, GETDATE()) = 1 BEGIN PRINT 'It is the weekend.' PRINT 'Get some rest on the weekend!' END ELSE BEGIN PRINT 'It is a weekday.' PRINT 'Get to work on a weekday!' END
WAITFOR 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.
RETURN is used to immediately return from a stored procedure or function.
BREAK ends the enclosing
WHILE loop, while
CONTINUE causes the next iteration of the loop to execute. An example of a
WHILE loop is given below.
DECLARE @i INT SET @i = 0 WHILE @i < 5 BEGIN PRINT 'Hello world.' SET @i = @i + 1 END
Changes to DELETE and UPDATE statements 
In Transact-SQL, both the DELETE and UPDATE statements allow a FROM clause to be added, which allows joins to be included.
This example deletes all
users who have been flagged with the 'Idle' flag.
DELETE users FROM users as u INNER JOIN user_flags as f ON u.id=f.id where f.name = 'idle'
BULK INSERT 
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 BULK INSERT results in better performance than processes that issue individual INSERT statements for each row to be added. Additional details are available in MSDN.
TRY CATCH 
Beginning with SQL Server 2005, Microsoft introduced additional TRY CATCH logic to support exception type behaviour. This behaviour enables developers to simplify their code and leave out @@ERROR checking after each SQL execution statement.
-- begin transaction BEGIN TRAN BEGIN TRY -- execute each statement INSERT INTO MYTABLE(NAME) VALUES ('ABC') INSERT INTO MYTABLE(NAME) VALUES ('123') -- commit the transaction COMMIT TRAN END TRY BEGIN CATCH -- rollback the transaction because of error ROLLBACK TRAN END CATCH
User-defined functions 
User-defined functions (UDFs) are routines that perform calculations/computations and return a scalar value (singular) or a table. UDFs can be embedded in queries, constraints, and computed columns. The code that defines a UDF may not cause side effects that affect the database state outside the scope of the function—that is, the UDF’s code is not allowed to modify data in tables or to invoke a function that has side effects (for example, RAND). In addition, the UDF’s code can only create table variables and cannot create or access temporary tables. Also, the UDF’s code is not allowed to use dynamic SQL.
Scalar UDFs return a single (scalar) value. They can be specified where scalar expressions are allowed—for example, in a query, constraint, computed column, and so on. Scalar UDFs have several syntactical requirements:
- They must have a BEGIN/END block defining their body.
- They must be schema qualified when invoked (unless invoked as stored procedures with EXEC, as in EXEC myFunction 3, 4).
- They do not allow omitting optional parameters (ones that have default values) when invoked; rather, you must at least specify the DEFAULT keyword for those.
T-SQL UDFs are typically faster than CLR UDFs when the main cost of their activity pertains to set-based data manipulation, as opposed to procedural logic and computations. In the function’s header you specify its name, define the input parameters, and define the data type of the returned value.
CREATE FUNCTION dbo.fn_get_car_name (@p_model VARCHAR(40)) RETURNS VARCHAR(100) AS BEGIN DECLARE @p_result VARCHAR(100); SET @p_result = (SELECT car + ' ' + model FROM cars WHERE model = @p_model); RETURN @p_result; END;
To test the function, run the following queries:
SELECT dbo.fn_get_car_name ('A-Class'); --or SELECT dbo.fn_get_car_name ('Navigator'); --or SELECT dbo.fn_get_car_name(model) FROM cars WITH (NOLOCK); --or EXEC fn_get_car_name @p_model='Navigator'; --or BEGIN DECLARE @name VARCHAR(100); SET @name = dbo.fn_get_car_name('Navigator'); print @name; END;
See also 
- Adaptive Server Enterprise (Sybase)
- PL/SQL (Oracle)
- PL/pgSQL (PostgreSQL)
- SQL/PSM (ISO standard) and SQL PL (IBM subset for DB2)
- "T-SQL tutorial video". Learn-with-video-tutorials. Retrieved 2013-04-14.