THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Eric Johnson

My Thoughts on SQL Server and Database Related Technologies.

New T-SQL Functionality in SQL Server 2008

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.

Published Monday, March 15, 2010 10:00 AM by ejohnson2010

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Arvind said:

Really its good to know that MS has included such features with its 2008 release of SQL server.

Thansk

March 22, 2010 7:13 AM
 

' said:

?

August 10, 2012 4:56 AM

Leave a Comment

(required) 
(required) 
Submit

About ejohnson2010

Eric (Microsoft SQL MVP) is the co-founder of Consortio Services, and the primary Database Technologies Consultant. His background in Information Technology is diverse, ranging from operating systems and hardware to specialized applications and development. He has even done his fair share of work on networks. Since IT is really just a way to support business processes, he has also acquired his MBA. All in all, he has 10 years of experience with IT, a great amount of which has been working with Microsoft SQL Server. Eric has managed and designed databases of all shapes and sizes. He has delivered numerous SQL Server training classes and webcasts as well as presentations at national technology conferences. Most recently, he presented at TechMentor on SQL Server 2005 Replication, Reporting Services, and Integration Services. In addition, he is active in the local SQL Server Community, serving as the President of the Colorado Springs SQL Server Users Group.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement