Recently I filed a bug regarding the MERGE statement in SQL Server 2008 via the Microsoft Connect website. The bug is that in certain circumstances, an update applied by the MERGE statement can bypass referential integrity constraints that point to nonclustered unique keys, and cause logical data corruption. The bug is applicable to SQL Server 2008 RTM. Microsoft provides a hotfix to this bug that you should take before using the MERGE statement in production in this scenario. Details on the bug and the hotfix can be found here.
To reproduce the bug, first run the following code:
set nocount on;
use tempdb;
go
drop table dbo.t2, dbo.t1;
go
create table dbo.t1
(
c1 int not null primary key,
c2 int not null unique
);
create table dbo.t2
(
c1 int not null,
c2 int not null references dbo.t1(c2),
primary key(c1, c2));
go
insert into dbo.t1(c1, c2) values(1, 1);
insert into dbo.t2(c1, c2) values(1, 1);
merge into dbo.t1
using (select 1 as c1) as d
on t1.c1 = d.c1
when matched then
update set t1.c2 = 2;
The MERGE statement succeeds in updating the row in t1 even though related rows exist in t2. This results in orphaned rows in t2.
I discovered this bug by accident while trying to reproduce another bug with MERGE that was sent to me by Rajeev Lahoty. The bug that Rajeev found is kind of the inverse of the previous bug—when you use a MERGE statement to update a referenced row key that has related referencing rows (prior to the update) without actually changing the key, the statement fails while it shouldn’t. Here’s the repro:
set nocount on;
use tempdb;
go
drop table dbo.t2, dbo.t1;
go
create table dbo.t1
(
c1 int not null primary key,
c2 int not null unique
);
create table dbo.t2