Worthwhile T-SQL Additions
Compound operators offer coding shortcuts, while the INSERT statement lets you ask for rows of data without wearing out the keyboard.
- By Eric Johnson
- 02/08/2010
Last time, we started looking at some of the new features offered in T-SQL in SQL Server 2008. This time, 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. The shorthand version is a little quicker to type and offers a cleaner piece of finished code. Here's a list of the compound operators:
+= Add EQUALS
-= Subtract EQUALS
*= Multiply EQUALS
/= Divide EQUALS
%= Modulo EQUALS
&= Bitwise AND EQUALS
^= Bitwise Exclusive OR EQUALS
|= Bitwise OR EQUALS
Starting in SQL Server 2008, you can now set a variable's value at the same time you declare it. The code will declare a variable named @ctr of type int and set its value to 100:
DECLARE @ctr int = 100
This was previously only possible with parameters, but now it works with all variable declarations.
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 haven't covered everything that has been added to T-SQL in SQL Server 2008, but these are few of the more useful little additions.
About the Author
Eric Johnson, SQL Server MVP, is the owner of Consortio Services in Colorado Springs providing IT systems management and technology
consulting. He is also the President of the Colorado Springs SQL Server
User Group. He can be contacted at www.consortioservices.com.