While we're waiting for the next SQL Server CTP, it thought I could mention a couple of small things already in the current CTP: Multiple row Insert and Declare-Set combined.
To insert multiple rows in SQL Server 2005 and before, you had to execute multiple INSERT statements, or use UNION. The first would look like this:
INSERT INTO MyTable (Col1, Col2)
VALUES (1, 'One')
INSERT INTO MyTable (Col1, Col2)
VALUES (2, 'Two')
INSERT INTO MyTable (Col1, Col2)
VALUES (3, 'Three')
And using UNION it would be
INSERT INTO MyTable (Col1, Col2)
SELECT 1, 'One'
UNION ALL SELECT 2, 'Two'
UNION ALL SELECT 3, 'Three'
In SQL Server 2008, this can be done like this:
INSERT INTO MyTable (Col1, Col2)
VALUES (1, 'One'), (2, 'Two'), (3, 'Three')
I did some quick performance testing and found that UNION and multiple inserts runs on about the same time, while the single inserts are slower. But this is only true as long as the numbers of rows being inserted isn't to high. If inserting 10 000 rows in one go, for instance, the individual inserts are actually faster, probably because of the amount of in-memory data the unions and the multiple row inserts produce, as well as that the last two commands run the whole batch within on large transaction.
Then, Declare-Set combined: It has long irritated me that I had to use two lines to declare a variable and assign a value to it. This is what it looks like i SQL Server 2005 and earlier:
DECLARE @var int
SET @var = 50
Now, in SQL 2008, we can do this:
DECLARE @var int = 50
We can also declare and assign to multiple variables in one go:
DECLARE @var1 int = 50,
@var2 varchar(10) = 'Hello!'
Pretty basic, and it's probably long overdue, but it's neat and it's finally here 