Merge issue: Attempting to set a non-NULL-able column’s value to NULL

In SQL server 2008 (serivce pack3), we are trying to use merge to insert new records in a table and the source data doesn’t contain any NULL values, but we get the exception “Attempting to set a non-NULL-able column’s value to NULL”.

After a lot of investigations I found some blog posts that points to a direction. There was a bug in service pack 1 that should have been fixed on cumulative update 7, and it occured when there was a table spool and the use of ISNULL(), BUT… in my case we didn’t have any ISNULL functions in the code. Googled a little bit more and found some Microsoft Connect items that confirms that the bug still exists in service pack 2, but now ti should have been fixed.

In the execution plan i could see that there was a table spool. In the support article Microsot mentioned that you could try to rewrite the query to remove the table spool. I my case the query was very simple and there was not possible to get it simplier and I had to play with some indexes on the source table to try to remove the table spool. No success!

If I removed a foreign key on the target table it worked! But I don’t want to remove the foreign key to solve the issues! 

When looking at the indexes of the target table, I found a non clustered index that had the same columns as the primary key index. There is no need for duplicated indexes, as a matter of fact it only decrease insert/update performance, and I decided to remove the index, just to reduce the overhead.

But wait a minute! The merge statement now works! That was strange and I looked at the execution plan and the table spool was gone! The table spool was caused by the update of the index.

If you have issues with Microsoft SQL server, feel free to contact any of our SQL server specialists and we will help you.

/Håkan Winther