Transact -SQL (or T-SQL), is Microsoft's extension to the SQL standard. It offers a host of system functions, local variable syntax and semantics, control flow constructs like WHILE and IF statements, TRY...CATCH error handling, and assorted enhancements to core SQL constructs. It's commonly used in one-off ad-hoc queries and in stored procedures to implement procedural programming style logic.

T-SQL syntax has always been more similar to Visual Basic than to C# - its syntax is verbose and uses keywords like BEGIN and END and NOT, rather than curly braces or other punctuation. Over the years the engineers at Microsoft have worked on making Visual Basic less verbose, adding a host of new operators and syntax to allow for more terse code. Such emphasis was not placed on T-SQL until just recently. T-SQL 2008 - the version of T-SQL used by Microsoft SQL Server 2008 - offers a number of new features that make the language more concise.

For starters, T-SQL 2008 now allows variables to be declared and initialized on a single line. Prior to T-SQL 2008 a variable declaration statement could not include an initial value. Instead, this initial value had to be set on a separate line, as the following code snippet shows:

With T-SQL 2008 you can now declare the variable and initialize it in the same statement:

Another enhancement added to T-SQL 2008 includes shorthand operators like +=, -=, *=, and so on. These operators act the same way as they do in Visual Basic or C#, namely by applying taking the value to the right of the operator, performing some action on it, and assigning the value to the variable on the left of the operator. For example, the following code snippet computes the first 20 Fibonacci numbers. The += operator is used in three places:

1. To compute the next number in the sequence by taking the @CurrentValue, adding the value of @TempLastValue, and storing that sum back into @CurrentValue.
2. To perform string concatenation to tack on each Fibonacci number to the end of the @FibOutput string.
3. To increment the value of the loop counter, @FibCount.

Note that the += operator can be used for addition (items 1 and 3 above) or for string concatenation (item 2), depending on the variable's type.

The output of the above script is:

The first 20 Fibonacci numbers: 1 1 2 3 5 8 13 21 34 55 89 144 233 377 610 987 1597 2584 4181 6765

The complete set of shorthand operators added to T-SQL 2008 follows:

Table 1: Shorthand operators added to T-SQL 2008

Row constructors are another new feature in T-SQL 2008. Prior to T-SQL 2008 an INSERT statement could add records to a table using one of two forms: INSERT INTO TableName (columns) VALUES(literalValues), or INSERT INTO TableName (columns) SELECT columns FROM TableName. The first form is used to insert literal values into a new record, whereas the latter form is used to insert zero to many new records based on the data returned from a SELECT query. The downside with the first form is that to needed to write an INSERT statement for each new record. For instance, to add four new records to the Employees table you'd write four INSERT statements, like so:

With T-SQL 2008 it is now possible to follow the INSERT statement with a comma-delimited list of row values, as the following snippet shows.

These parenthesized statements following the INSERT clause are referred to as row constructors. In addition to INSERT statements, row constructors may also appear in the FROM clause of a SELECT statement as a derived table. The following example shows row constructors used in this manner. Note that when using a derived table whose rows are constructed via row constructors you must specify the column names of the derived table (Name, Email, and Salary in the example below).

T-SQL 2008 has incorporated a number of new language features that provide for a more concise syntax. Be sure to take advantage of this abbreviated syntax when working with Microsoft SQL Server 2008.

Further Reading

• Pro T-SQL 2008 Programmer's Guide, by Michael Coles. Published by Apress, 2008.
• SQL Server 2008 New Features - Row Constructors

About Scott Mitchell

Scott Mitchell, author of eight ASP/ASP.NET books and founder of 4GuysFromRolla.com, has been working with Microsoft Web technologies since 1998. Scott works as an independent consultant, trainer, ... This author has published 16 articles on DotNetSlackers. View other articles or the complete profile here.