Transact-SQL Improvements with SQL Server 2008, Part 1
- Initializing and Incrementing Variables
- Inserting Multiple Rows with a Single Statement
- Merging Multiple Data Sets
- Separate Date and Time Data Types
- New Date- and Time-Related Functions
Like this article? We recommend
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:
SET @i -= 3 /* reduce the value by 3 */ SET @i *= 2 /* multiply the value by 2 */ SET @i /= 5 /* divide the value by 5 */
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:
DECLARE @i INT = 1, @a INT = 2, @b INT = 3 SELECT @i +=2, @a -=5, @b *=3 SELECT @i, @a, @b
Results:
|
3 |
-3 |
9 |
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:
DECLARE @i INT = 1, @a INT = 2 SELECT @i +=@a, @a -=@i SELECT @i, @a
Result:
|
3 |
-1 |