Search This Blog & Web

Sunday, July 26, 2009

SQL Server 2008 Katmail new features (Declaring and initializing variables)

This is my first post for SQL Server 2008 update features. We are going to start complete list of new features according to my following link

We declare and assign value to a variable in 2 different statements before SQL Server 2008 update. Lets see example

-- declare variables
DECLARE @date DATETIME
DECLARE @i INT

-- initialize
SELECT @date = GETDATE(), @i = 10

Now with SQL server 2008 we can declare an initialize variables in a single TSQL statement. see this example:

-- sql server 2008 - Declare and initialize in a single statement
DECLARE @date DATETIME = GETDATE(), @i INT = 5;

This is a great feature in a way to easy the code and assigning it value. Another interesting feature is the support for Compound Assignment as we read in C++ and VB 6. If we have a little idea then it is a appreciable change. See following example

DECLARE @i INT
SELECT @i = 10

SELECT @i = @i + 1 -- i = 11 now
SELECT @i = @i - 2 -- i = 9 now
SELECT @i = @i * 3 -- i = 27 now

SELECT @i AS newValue

----- Here is output-----
----------
newValue
-----------
27

There is another very good enhancement in Katmai. We can insert multiple records in a single statement that we do in different insert statements in sql server 2005. See below example of sql server 2005

Declare @table Table(a int, b int)
Insert into @table values (1,2)
Insert into @table values (2,2)
Insert into @table values (3,3)
Insert into @table values (3,4)

Now how can we do that in sql server 2008 is defined in following example

DECLARE @table TABLE ( a INT, b INT )
INSERT INTO @table VALUES (1,1), (2,2), (2,3),(3,3),(3,4)

I will discuss more updates in more posts

To see complete list of update for related posts visit
http://sqlservercoollinks.blogspot.com/2009/07/new-features-in-sql-server-2008-katmai.html

No comments: