New T-SQL Functionality in SQL Server 2008


We need 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. There are few of the new features offered in T-SQL in SQL Server 2008.  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)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: