SQL Server MERGE + IGNORE_DUP_KEY = ON

SQL Server MERGE + "IGNORE_DUP_KEY = ON"

                                                                         I'm huge fan of SQL Server Merge command and I'm no hesitation to recommended SQL Server Merge DML statement, if you have INSERT,UPDATE and DELETE at once and with validate not exists records.But recently one of our developer reported Insert records will fail with Duplicate key error but they already have "IGNORE_DUP_KEY = ON" their target table.Initially I was hope we can insert duplicate records without any error if you have  "IGNORE_DUP_KEY = ON" but I was wrong that time. Just I have read SQL Server MERGE  Syntax and remarks on MSDN. And I seen Microsoft mention clearly "If IGNORE_DUP_KEY is set to ON for any unique indexes on the target table, MERGE ignores this setting." Well... so no argument on that :) . Long time back (around 5 years ) I have publish article on SQLServerCentral.com(one of my favourice web site) about "Understanding the MERGE DML Statement in SQL Server 2008" and I have not include this on my article. Any way I'm recommended you to read this article if you also still new with SQL Server MERGE command.

Well let's try to reproduce "duplicate key" Error     

USE [tempdb]
GO
IF  EXISTS (SELECT 1 FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[tblOffsetAndFetch]') AND [type] in (N'U')) BEGIN
    DROP TABLE [dbo].[tblOffsetAndFetch];
END  
GO
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[tblOffsetAndFetch]') AND [type] in (N'U'))
BEGIN
    CREATE TABLE [dbo].[tblOffsetAndFetch](
        [id] [int] NOT NULL,
        [name] [varchar](50) NULL,
        [cDate] [datetime] NULL,
        [intData] [int] NULL,
        [decimalData] [decimal](21, 5) NULL,
        [floatData] [float] NULL,
    PRIMARY KEY CLUSTERED
    (
        [id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY];
END
GO
SELECT * FROM [dbo].[tblOffsetAndFetch];
GO
MERGE [dbo].[tblOffsetAndFetch] AS A
USING (SELECT '01' [id],'Records 01' [name],Getdate() [cDate],01 [intData],1.1 [decimalData],1.11 [floatData]
UNION
SELECT '01' [id],'Records 02' [name],Getdate() [cDate],02 [intData],2.2 [decimalData],2.22 [floatData]) AS I
ON (A.id = I.id)
WHEN NOT MATCHED
THEN INSERT VALUES(I.id, I.[name], I.[cDate], I.[intData], I.[decimalData], I.[floatData]);
GO
SELECT * FROM [dbo].[tblOffsetAndFetch];
GO

Results,

 (0 row(s) affected)
Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK__tblOffse__3213E83FABB9779A'. Cannot insert duplicate key in object 'dbo.tblOffsetAndFetch'. The duplicate key value is (1).
The statement has been terminated.

(0 row(s) affected)

Well! hope now you will understand the above error occur situation.And I have found some helpfull artical on SQL Server MERGE DML statement and I hope it will help you all, 

•  Indexed view is not updated on data changes in base table
• If you are using indexed views and MERGE, please read this!

Cheers...
and Most welcome your comments and ideas...