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://blog.sqlauthority.com)
19 Comments. Leave new
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.
Thanks dave. Its very useful to me to take the unique records.
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
Just a small update, it works with Unique Clustered Index too.
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?
Pinaldave,
Which version of SQL Server did you use? Did you use 2000, 2005, or 2008? I’m very curious about this.
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
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
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.
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
Can I use IGNORE_DUP_KEY property on Clustered index
Hey man.
Yo are doing wonderful job.
Thanks for sharing,
Ashok.
HI Anand U R Msg is OK.
When ever you use UNIQUE CLUSTERED INDEX .At this time record will be printed DECENDING Order only.
It helped me to fix one of the issue in our environment thank you sir..
Excellent explanation… Thanks pinal…
thank you .
it worked fine for table variable too.
Thanks @gayan