Transact-SQL Totally Explained

Everything about T-sql totally explained

Transact-SQL (T-SQL) is Microsoft's and Sybase's proprietary extension to the SQL language. Microsoft's implementation ships in the Microsoft SQL Server product. Sybase uses the language in its Adaptive Server Enterprise, the successor to Sybase SQL Server. In order to make it more powerful, SQL has been enhanced with additional features such as:

Control-of-flow language
Local variables
Various support functions for string processing, date processing, mathematics, etc.
Improvements to DELETE and UPDATE statements

Flow control

Keywords for flow control in Transact-SQL include BEGIN and END, BREAK, CONTINUE, GOTO, IF and ELSE, RETURN, WAITFOR, and WHILE. IF and ELSE allow conditional execution. This batch statement will print "weekend" if the current date is a weekend day, or "weekday" if the current date is a weekday.

IF DATEPART(dw, GETDATE) = 7 OR DATEPART(dw, GETDATE) = 1
   PRINT 'It is the weekend.'
ELSE
   PRINT 'It is a weekday.'

BEGIN and 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!'
END
ELSE
BEGIN
   PRINT 'It is a weekday.'
   PRINT 'Get to work!'
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.

Local variables

Local variables are so named because they're local to the script executing them. Transact SQL doesn't support user-defined global variables. DECLARE will declare a variable, giving it a name and a type. The SET statement can be used to provide a value, and the variable may be used in a statement by referencing its name.
This script declares a variable as an integer, initializes it, then uses WHILE to execute a loop.

DECLARE @Counter INT
SET @Counter = 10
WHILE @Counter > 0
BEGIN
   PRINT 'The count is ' + CONVERT(VARCHAR(10), @Counter)
   SET @Counter = @Counter - 1
END

The body of the loop will print a message including the value of the variable, and then decrement the counter.
A variable may be initialized as the result of a statement, like this:

DECLARE @ArticleCount INT
SELECT @ArticleCount = COUNT(*) FROM Articles

   INSERT INTO SizeLog (SampleTime, ArticleCount) VALUES (GETDATE, @ArticleCount)

which will get the count of rows in the Articles table, then insert a row including that count and the current clock time into the SizeLog table.

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 delete all users who have been flagged with the 'Idle' flag.

DELETE users 
  FROM users as u
  JOIN user_flags as f
    ON u.id=f.id
 WHERE f.name = 'Idle'

BULK INSERT

BULK INSERT is a Transact-SQL statement which 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 which issue individual INSERT statements for each row to be added. Additional details are available on Microsoft's MSDN page

Further Information

Get more info on 'T-sql'.

External Link Exchanges

Do you know how hard it is to get a link from a large encyclopaedia? Well we're different and will prove it. To get a link from us just add the following HTML to your site on a relevant page:

<a href="http://transact-sql.totallyexplained.com">Transact-SQL Totally Explained</a>

Then simply click through this link from your web page. Our crawlers will verify your link, extract the title of your web page and instantly add a link back to it. If you like you can remove the words Totally Explained and embed the link in article text.
As long as your link remains in place, we'll keep our link to you right here. Please play fair - our crawlers are watching. Your site must be closely related to this one's topic. Any kind of spamming, dubious practises or removing the link will result in your link from us being dropped and, potentially, your whole site being banned.