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.

Solarwinds

Option 1: IGNORE_DUP_KEY = OFF

SQL SERVER - Unique Nonclustered Index Creation with IGNORE_DUP_KEY = ON ignoredup1

Option 2: IGNORE_DUP_KEY = ON

SQL SERVER - Unique Nonclustered Index Creation with IGNORE_DUP_KEY = ON ignoredup2

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://blog.sqlauthority.com)

Solarwinds
, ,
Previous Post
SQLAuthority News – TechDays Session at Infosys Mysore 2009 – Change Data Capture and PowerPivot
Next Post
SQL SERVER – Fix: Error: 262 : SHOWPLAN permission denied in database

Related Posts

19 Comments. Leave new

  • Marko Parkkola
    January 4, 2010 12:37 pm

    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.

    Reply
    • Marko,

      I have follow up post demonstrating the usage of this particular feature.

      Kind Regards,
      Pinal

      Reply
      • 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

  • Thanks dave. Its very useful to me to take the unique records.

    Reply
  • 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

    Reply
  • Just a small update, it works with Unique Clustered Index too.

    Reply
  • 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?

    Reply
  • Pinaldave,

    Which version of SQL Server did you use? Did you use 2000, 2005, or 2008? I’m very curious about this.

    Reply
  • Chavoux Luyt
    July 1, 2010 5:53 pm

    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

    Reply
  • 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

    Reply
  • 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.

    Reply
  • 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

    Reply
  • Can I use IGNORE_DUP_KEY property on Clustered index

    Reply
  • Hey man.

    Yo are doing wonderful job.

    Thanks for sharing,
    Ashok.

    Reply
  • HI Anand U R Msg is OK.
    When ever you use UNIQUE CLUSTERED INDEX .At this time record will be printed DECENDING Order only.

    Reply
  • It helped me to fix one of the issue in our environment thank you sir..

    Reply
  • Excellent explanation… Thanks pinal…

    Reply
  • thank you .
    it worked fine for table variable too.

    Reply

Leave a Reply

Menu