New and Changed SQL Server 2005 commands: Part1

I know I don’t often talk about code, but I have recently had the good fortune to get back into playing around with the new version of SQL Server 2005. I think I’m going to do a few small posts on some of the new things I’ve found that I like.

Insert and Merge, finally your can ‘Upsert’!

Upsert is a mythical feature where you can insert and update data with one command. It's not quite in one command but the effect is the same with a simple set of syntax.

MERGE INTO DavesTable

            USING DavesTempTable

                        ON DavesTable.MatchingField1 = DavesTempTable.MatchingField1

WHEN MATCHED THEN

            UPDATED UpdatedField1 = DavesTempTable.UpdatedField1

WHEN NOT MATCH THEN

            INSERT VALUES(DavesTempTable.MatchingField1, DavesTempTable.UpdateField1)

Although it’s nice to finally have this feature and the syntax is simple and elegant I’m wondering if many people will actual use it in anger.

Now the TOP command has always been a great feature but what has always been a right pain is that it has to be a constant in SQL Server 2000 which has really limited it’s use.

Now this syntax is valid!

-- show the first x products names in alphabetical order.

DECLARE @x bigint

SET @x = 25

SELECT TOP (@x) ProductID, Name

FROM Product

ORDER BY NAME

Just like the previous version of the TOP command it was well used in scalar statements. With the ability to pass the value to the statement from the outside I can see this going to be a very powerful new feature used for reporting especially as the PERCENT suffix is still valid. The only departure from the previous version is that you will need to use brackets if the value is a variable, use a constant and you won’t need them.