In my most recent posts I have looked at a few of the new features offered in T-SQL in SQL Server 2008. In this post, I want to take a closer look at some of the smaller additions, but additions that are likely to pack a big punch in terms of efficiency. First let’s talk a little about compound operators. This is a concept that has been around in programming languages for a long time, but has just now found its way into T-SQL. For example, the += operator will add the values to the current variable and then assign the new value to the variable. In other words, SET @ctr += 1 is functionally the same as SET @ctr = @ctr + 1. This shorthand is a little quicker to type and offers a cleaner piece of finished code. The complete list of compound operators is below.
+= Add EQUALS
-= Subtract EQUALS
*= Multiply EQUALS
/= Divide EQUALS
%= Modulo EQUALS
&= Bitwise AND EQUALS
^= Bitwise Exclusive OR EQUALS
|= Bitwise OR EQUALS
Stating in SQL Server 2008, you can now set a variable’s value at the same time you declare it. For example the following line of code will declare a variable named @ctr of type int and set its value to 100. This was previously only possible with parameters, but now it works with all variable declarations.
DECLARE @ctr int = 100
Last, but certainly not least, the INSERT statement will accept multiple row predicates on the VALUES clause. In other words, I can insert multiple rows with a single INSERT statement. The following example shows the old syntax and the new multi-row INSERT syntax.
Pre-SQL Server 2008
INSERT SALES (customer_id, year, sales_amt) VALUES (1,2007,25000)
INSERT SALES (customer_id, year, sales_amt) VALUES (1,2008,22000)
INSERT SALES (customer_id, year, sales_amt) VALUES (1,2009,15000)
INSERT SALES (customer_id, year, sales_amt) VALUES (2,2007,35500)
INSERT SALES (customer_id, year, sales_amt) VALUES (2,2008,56800)
INSERT SALES (customer_id, year, sales_amt) VALUES (2,2009,65600)
SQL Server 2008 Multi-Row INSERT
INSERT SALES (customer_id, year, sales_amt)
VALUES (1,2007,25000),
(1,2008,22000),
(1,2009,15000),
(2,2007,35500),
(2,2008,56800),
(2,2009,65600)
We certainly havn’t covered everything that has been added to T-SQL in SQL Server 2008, but these a are few of the more useful little additions.