MERGE Stmt : Search condition on a non-key column and an ORDER BY in source dervied table breaks MERGE completely
I struggled for quite some time to troubleshoot this issue when i had a ORDER BY clause in my source table (Derived table) and the column i used in search condition (ON condition) doesn't have a unique constriant/index the MERGE simply breaks. I have to say that because i have observed really strange behaviours For example : "Attempting to set a non-NULL-able column's value to NULL.", some times it throws a similar error message with a column which is nullable, it runs fine if i remove the ORDER BY , it runs fine if reduce the length of a nvarchar field in my source table ! etc.,
But the good news is i have managed to create a simple repro so that you can re produce it in-house and see what's going wrong.
The re-pro i have created for you produces the error only when the source table doesn't have a PK but in my actual scneario i do have a PK but it was failing with the same error and could fix it by creating unique index on the column i used on the search condition.
Upvotes: 4<=-=Mar 10 2010 10:07AM=-=>
Thanks for submitting this issue Mei. We had also stumbled on this issue and this has been fixed in the next release for SQL Server. Since you have a work around, I assume this is not a blocking issue for you for now?
Glad to hear that it has been fixed already and avaialble in next release. I’m going ahead with the workaround as of now just that it was driving me crazy for quite somtime on troubleshooting.
Thanks a lot !
Mei<=-=Mar 30 2010 6:24PM=-=>
We had a recent QFE (409769) which effectively fixes this problem. (We mark all computed columns in sorts and other work tables as nullable, even if the compiler thinks that they aren�t nullable.
Please see if this addresses your concern
Confirmed to be FIXED in SQL 2008 build 5869 (X64)
Confirmed to be FIXED in SQL 2008 R2 build 4321 (X64)
Confirmed to be FIXED in SQL 2012 build 5522 (X64)
Confirmed to be FIXED in SQL 2014 build 2480 (X64)