MERGE statement bug when INSERT/DELETE used and filtered index by Vladimir Moldovanenko

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