Saturday, January 12, 2008

A question about and answer to the MERGE Statement and the CLR version in SQL Server 2008

I was giving a presentation on the new programmability features in SQL Server 2008 when the following question popped up:"Does the MERGE statement support multiple WHEN MATCHED clauses?"

As you know it is possible to say "WHEN MATCHED AND myField = 0 THEN...", so it would make sense to have multiple WHEN MATCHED statements because depending on the criteria you could have different actions. Since you see this blog post you might have guessed that it is not the obvious answer. Trying to add a second WHEN MATCHED statement will result in the following pretty clear error: "An action of type 'WHEN MATCHED' cannot appear more than once in a 'UPDATE' clause of a MERGE statement".

A second popular question was: "Which version of the .NET framework is included in SQL Server 2008?". This question has been answered by Bob Beauchemin on his blog with a very detailed explanation. Basically it loads the latest CLR version but there is a little catch which is explained by Bob so I recommend you read his post.