Among the large number of new features in SQL Server 2008 there are two, almost insignificant features, that I use almost constantly. These are variable initialization and the values clause of insert statements that now accept multiple rows.

When playing with SQL one inevitably declares some variables. After the declaration, and as per the SQL standard, we can use the SET statement to assign a value to a variable:

DECLARE @a int, @b int

SET @a = 1

SET @b = 2

If we do not stick to the standard, then we can use a SELECT statement to assign values to several variables in a single statement:

SELECT @a = 1, @b =2

Sometimes, using SELECT is indeed our only choice, e.g. when we want to get both the row count and the error code from the last statement. There is no SET alternative to the following statement:

SELECT @RowCount = @@ROWCOUNT, @ErrCode = @@ERROR

Anyway, SQL Server 2008 allows us to declare and initialize variables in a single statement, like:

DECLARE @a int = 1, @b int = 2

While I still do not understand why saving on the number of lines of code on my screen makes me feel better (two 19” screens provide me with plenty of space 🙂 ), somehow I like this feature.

Another small extension to T-SQL that prevents me from including more typos is adding multiple rows in an insert statement:

DECLARE @table TABLE ( a INT, b INT )

INSERT INTO @table VALUES (1,1), (2,2), (3,3)

The INSERT statement here inserts three rows into the @table. This is something I got used to before, and was missing from SQL Server.

It is nice to see that in SQL Server 2008 Microsoft is adding such syntactic sugar in addition to the number of other improvements, like spatial data types, data compression, transparent data encryption, etc. I’ve been talking about these improvements at VBUG and NxtGen events during the last few weeks, so in my blog I’d also like to say thanks to the people who attended and organized these events.