SQL SERVER – Unique Nonclustered Index Creation with IGNORE_DUP_KEY = ON

In one of my recent training course, I was asked question regarding what is the importance of setting IGNORE_DUP_KEY = ON when creating unique nonclustered index.

Here is the short answer: When nonclustered index is created without any option the default option is IGNORE_DUP_KEY = OFF, which means when duplicate values are inserted it throws an error regarding duplicate value. If option is set with syntaxIGNORE_DUP_KEY = ON when duplicate values are inserted it does not thrown an error but just displays warning.

Let us try to understand this with example.

Option 1: IGNORE_DUP_KEY = OFF

Option 2: IGNORE_DUP_KEY = ON

You can find complete script for the above example.

USE tempdb
GO
CREATE TABLE DupIndex (ID INT,
FirstName VARCHAR(100),
LastName VARCHAR(100),
City VARCHAR(100))
GO
--Create Unique Index on Table
CREATE UNIQUE NONCLUSTERED INDEX [IX_DupIndex_FirstName] ON [dbo].[DupIndex] (
[FirstName] ASC
) ON [PRIMARY] GO
INSERT INTO DupIndex (ID,FirstName,LastName,City)
VALUES(1,'John','Doe','LA');
INSERT INTO DupIndex (ID,FirstName,LastName,City)
VALUES(2,'Joe','Doe','LA');
/*
Following line will throw an error
Msg 2601, Level 14, State 1, Line 6
Cannot insert duplicate key row in object 'dbo.DupIndex'
with unique index 'IX_DupIndex_FirstName'.
*/
INSERT INTO DupIndex (ID,FirstName,LastName,City)
VALUES(3,'John','Doe','LA');
GO
-- Table with contain only two rows
SELECT *
FROM DupIndex
GO
DROP TABLE DupIndex
GO
--
USE tempdb
GO
CREATE TABLE DupIndex (ID INT,
FirstName VARCHAR(100),
LastName VARCHAR(100),
City VARCHAR(100))
GO
--Create Unique Index on Table with IGNORE_DUP_KEY set ON
CREATE UNIQUE NONCLUSTERED INDEX [IX_DupIndex_FirstName] ON [dbo].[DupIndex] (
[FirstName] ASC
) WITH (IGNORE_DUP_KEY = ON) ON [PRIMARY] GO
INSERT INTO DupIndex (ID,FirstName,LastName,City)
VALUES(1,'John','Doe','LA');
INSERT INTO DupIndex (ID,FirstName,LastName,City)
VALUES(2,'Joe','Doe','LA');
/*
Following line will throw warning only & will not throw error
Duplicate key was ignored.
*/
INSERT INTO DupIndex (ID,FirstName,LastName,City)
VALUES(3,'John','Doe','LA');
GO
-- Table with contain only two rows
SELECT *
FROM DupIndex
GO
DROP TABLE DupIndex
GO


Reference: Pinal Dave (https://darkslategrey-bat-805937.hostingersite.com)