Summary: Investigating an optimiser transformation that exposes a bug in SQL Server’s MERGE implementation.
I came across a Connect item today (by fellow SQL Server Central member ‘ALZDBA’) describing how using a combination of relatively new features can produce incorrect results or even an access violation inside SQL Server.
Reproducing the bug
We’ll look first at how MERGE can produce incorrect an output. We’ll need two tables: one that contains two rows of existing data; and a second that contains four rows of change information. The overall process looks like this:
This is a classic MERGE (or “upsert”) requirement. If the row already exists, the new value is added to it, otherwise a new row is inserted. Here’s the code to generate the sample tables and data: Read More...