0 comments
Business Intelligence 
We use a couple of time the Merge Statement (I have write a blog post about that earlier) in different projects. I mentioned that not everybody is aware of some specific requirements of the Merge Statement.
- 1) The Merge statement is a single row execution statement.
Because the Merge statement is a single row statement, you have to start a transaction by yourself to be aware that the total dataset is transformed with the Merge Statement. If by some reason the statement failed in the middle of the dataset, it is not totally rolled back by default.
- 2) Related to the first point, the MERGE statement may not enforce a foreign key constraint when the statement updates a unique key column that is not part of a clustering key and there is a single row as the update source in SQL Server 2008
Microsoft defined this as a bug and a fix is available http://support.microsoft.com/kb/956718