SQL Server 2008 introduced some significant improvements in the Transact-SQL language. In part 1 of this two-part series, database administration expert Baya Dewald discusses some of the most exciting of the new and changed functionality for working with variables, managing date and time values, and row constructors.

Like this article? We recommend

Like this article? We recommend

Transact-SQL is a powerful programming language for developing and managing SQL Server databases. Microsoft significantly enhanced the language in the last few years, and many improvements were introduced in SQL Server 2008. In this two-part series, I discuss some of the improvements I find most exciting and useful.

Initializing and Incrementing Variables

With SQL Server 2008, developers can declare and initialize variables in the same statement. For example, the following statement declares variable @i of the integer data type and assigns it a value of zero:

DECLARE @i INT = 0

In the past, all variable declarations had to be separated from initial value assignments. Further, we can now use compound assignment operators, similar to the techniques of other languages. For example, we could use the next statement to increment the value of @i by one:

SET @i += 1

You can use similar statements to decrement, multiply, and divide the value by any number:

You can also combine the bitwise AND, bitwise OR, bitwise XOR, and modulo operators with assignment. If you need to use multiple variables, you can use a SELECT statement to assign values to all of them in a single statement:

We could also use variables, parameters, or column values to assign values to other variables. The next example increments the @i variable by the value of @a and then subtracts the resulting @i value from @a, to derive the new value of @a: