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 (http://blog.SQLAuthority.com)

19 thoughts on “SQL SERVER – Unique Nonclustered Index Creation with IGNORE_DUP_KEY = ON

  1. But if I’m creating UNIQUE index, why would I want it NOT to throw an error when trying to add duplicate value? I mean, I don’t see any situation where I wouldn’t want it to throw an error.

    Like

      • thanks u r index query but i get error when i am trying to create UNIQUE NONCLUSTERED INDEX they give me

        Error
        —-Server: Msg 170, Level 15, State 1, Line 4
        Line 4: Incorrect syntax near ‘(‘.

        GO
        CREATE TABLE DupIndex (ID INT,
        FirstName VARCHAR(100),
        LastName VARCHAR(100),
        City VARCHAR(100))
        GO

        Go
        CREATE UNIQUE NONCLUSTERED INDEX [IX_DupIndex_FirstName]
        ON [dbo].[DupIndex]
        (
        [FirstName] ASC
        ) WITH (IGNORE_DUP_KEY = ON) ON [PRIMARY]
        GO

        Plz Help me ………..
        and
        How to data retrieval fast in server to client

        Like

  2. Hello Marko,

    After implementing the uniqueness of a column if we take the below requirement:

    When a duplicate value arrive in an INSERT statement, Should the INSERT statement failed without inserting a single record or just ignore the records of duplicate value?

    The answer ‘yes’ or ‘no’ depends on business and SQL Server provides us an handy option to implement that is “IGNORE_DUP_KEY”.

    Regards,
    Pinal Dave

    Like

  3. Pingback: SQL SERVER – Unique Nonclustered Index Creation with IGNORE_DUP_KEY = ON – A Transactional Behavior Journey to SQL Authority with Pinal Dave

  4. In what scenarios would you want to turn the IGNORE_DUP_KEY setting ON?

    From a data consistency standpoint, I would want it ‘OFF’, but perhaps there are other uses for it.

    What do you guys use the ON setting for?

    Like

  5. Thanks Pinal

    So do I understand correctly that even with IGNORE_DUP_KEY=ON, I still will not have duplicates in the table? If so, this is exactly what I wanted… then I don’t have to check my SELECT data for duplicates before inserting!

    Cheers
    Chavoux

    Like

  6. Hi Pinal,

    I have came across a problem in my inhouse website.

    With Unique, Non-Clustered index and IGNORE_DUP_KEY=ON, duplicate value is inserted in a column, without throwing any error.

    This happened only once. Otherwise, it is running as expected. When I tried to insert duplicate value, it does not allow, which is correct.

    Only this one record has got duplicate value which is puzzling me. I am trying to figure out the reason.

    Any help appreciated.

    Thanks

    Like

  7. Hi Dave, thanks for your excellent blogs – you always seem to answer questions simply and succinctly, without being patronising or drowning everything in endless complexity, or assuming that people already know almost everything – if tha was the case, they wouldn’t need to ask, would they?

    My extension to this question is, if you have a duplicate key, is there a standard way to identify the duplicate? The “duplicate” message always drives me mad, since SQL Server must know what the duplicate is or it couldn’t say it was a duplicate. So why not add the key values to the duplicate message, to give you a fighting chance of doing something about it?

    Not so hard on single inserts, but can be a nightmare for bulk inserts, esp where you may not have much control about the data contents.

    Like

  8. Hi Pinal,
    Thanks for the article.
    I’ve a question. Can i Insert duplicate values on a non clustered indexed column.
    If true can you please expalin how.

    Thanks in advance

    Like

  9. Pingback: SQL SERVER – Weekly Series – Memory Lane – #010 « SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s