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
When to Use IGNORE_DUP_KEY and What to Watch For
The behavior is easy to miss in a demo, so let me spell it out. With IGNORE_DUP_KEY = ON, a multi-row INSERT that contains a duplicate does not fail. SQL Server skips the duplicate rows, inserts the rest and returns the message ‘Duplicate key was ignored.’ With the default OFF, the whole statement fails and none of its rows go in.
Where does this help? Mostly in load jobs where the incoming feed may repeat keys and you are happy to keep whatever row is already in the table. It saves you from writing a NOT EXISTS check for every insert. To see it for yourself, create a small table with a unique index that uses the option, then insert three rows where two share a key. You get two rows and one warning, not an error.
Now the things to watch for:
- It is silent data loss by design. An application that ignores warnings will never know rows were skipped.
- It only affects inserts. An UPDATE that creates a duplicate still fails with an error.
- You cannot turn it on for non-unique indexes, filtered indexes or indexes on views.
- It hides the root cause. If duplicates should never happen, a loud error is often the better friend.
To check an existing index, look at the matching column in sys.indexes. Use the option on purpose, write down why, and make sure the team knows skipped rows are expected.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.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.
Marko,
I have follow up post demonstrating the usage of this particular feature.
Kind Regards,
Pinal
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.
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