Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

MERGE statement bug when INSERT/DELETE used and filtered index

MERGE statement will produce
Msg 2601, Level 14, State 1, Line 77
Cannot insert duplicate key row in object 'dbo.Test' with unique index 'UIXF_Test'.
The statement has been terminated.

use tempdb
go
/*
IF object_id('dbo.Test', 'U') IS NOT NULL
DROP TABLE dbo.Test
go
IF object_id('dbo.Src', 'U') IS NOT NULL
DROP TABLE dbo.Src
go
*/
IF object_id('dbo.Test', 'U') IS NULL
BEGIN
CREATE TABLE dbo.Test
(
intID int NOT NULL IDENTITY PRIMARY KEY
,sysID int NOT NULL
,ioID int NOT NULL
,Code nvarchar(10) NOT NULL
)
INSERT dbo.test (sysID, ioID, Code) VALUES (1, 1, 'A')

CREATE UNIQUE NONCLUSTERED INDEX [UIXF_Test] ON [dbo].[Test]
(
[sysID] ASC,
[ioID] ASC
)
WHERE ([ioID] IN ((1)))
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [Primary]

CREATE TABLE dbo.Src
(
intID int NOT NULL IDENTITY PRIMARY KEY
,sysID int NOT NULL
,ioID int NOT NULL
,Code nvarchar(10) NOT NULL
)
INSERT dbo.Src(sysID, ioID, Code) VALUES (10, 1, 'B')

END

DECLARE @sysID int = 1
,@ioID int = 1


BEGIN TRAN;

/*
WITH d AS
(
SELECT *
FROM dbo.Test d
WHERE d.sysID = @sysID
AND d.ioID = @ioID
)
MERGE INTO d
USING
(
SELECT
@sysID AS sysID
,@ioID AS ioID
,'B' as Code
) s
ON d.sysID = s.sysID
AND d.ioID = s.ioID
AND d.Code = s.Code
WHEN NOT MATCHED BY SOURCE THEN DELETE
WHEN NOT MATCHED BY TARGET THEN INSERT
(sysID
,ioID
,Code)
VALUES
(s.sysID
,s.ioID
,s.Code)
OUTPUT $action, inserted.*, deleted.*
;
*/

WITH d AS
(
SELECT *
FROM dbo.Test d
WHERE d.sysID = @sysID
AND d.ioID = @ioID
)
MERGE INTO d
USING
(
SELECT
@sysID AS sysID
,@ioID AS ioID
,Code
FROM dbo.Src
) s
ON d.sysID = s.sysID
AND d.ioID = s.ioID
AND d.Code = s.Code
WHEN NOT MATCHED BY SOURCE THEN DELETE
WHEN NOT MATCHED BY TARGET THEN INSERT
(sysID
,ioID
,Code)
VALUES
(s.sysID
,s.ioID
,s.Code)
OUTPUT $action, inserted.*, deleted.*
;

IF @@TRANCOUNT > 0 ROLLBACK

If unique index is removed, then MERGE performs modification correctly

Forum discussion:

http://social.technet.microsoft.com/Forums/en-US/transactsql/thread/32727b47-fd03-4033-8140-510876ccada2

0 votes
Sign in
(thinking…)
Sign in with: Microsoft
Signed in as (Sign out)

We’ll send you updates on this idea

AdminMicrosoft SQL Server (Product Manager, Microsoft Azure) shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

Upvotes: 31

<=-=Dec 6 2010 10:50AM=-=>

Hi
thanks for reporting this issue with the Merge statement/Filtered index. We are currently looking into this and will provide updates when we have more information here.

SQL Server.

<=-=Apr 11 2011 7:49AM=-=>

Are you still looking into this? Any feedback?

<=-=Aug 3 2011 10:54AM=-=>

Hello Vladimir,

Sorry it took so long to respond on this item! I acknowledge that this is a bug in our code.

The fix for this bug requires changes that can introduce potential performance and correctness regressions so it will take some time before you will see this item as fixed.

Thank you and best regards,
Boris Baryshnikov.
SQL Server Engine

<=-=Apr 24 2015 8:27AM=-=>

Confirmed the bug is still present in SQL 2014 build 2480 (X64).

Confirmed that the Paul White OPTION (QUERYTRACEON 8790) workaround Vladimir listed still works on that version.

<=-=May 27 2015 2:23PM=-=>

SQL 2014 SP1 still has this bug.

<=-=Jul 15 2017 6:04AM=-=>

This seems to be fixed now, test scenario works fine on
Microsoft SQL Server 2016 (SP1-CU3) (KB4019916) – 13.0.4435.0 (X64)
Apr 27 2017 17:36:12
Copyright © Microsoft Corporation
Developer Edition (64-bit) on Windows 10 Pro 6.3 <X64> (Build 15063: )

0 comments

Sign in
(thinking…)
Sign in with: Microsoft
Signed in as (Sign out)
Submitting...

Feedback and Knowledge Base