When should the IGNORE_DUP_KEY option be used on an index?
It's definitely an atypical setting to enable for an index. I don't think you're going to find many people who jump up and talk about using it.
Of course, there are a couple helpful posts on the subject by Paul White:
- IGNORE_DUP_KEY slower on clustered indexes
- A creative use of IGNORE_DUP_KEY
If your concern is around upsert patterns (or something similar), this article by Michael Swart is really informative:
- SQL Server UPSERT Patterns and Antipatterns
Every feature has a use case, and IGNORE_DUP_KEY is no exception.
As I explain in IGNORE_DUP_KEY slower on clustered indexes, this option only potentially benefits performance (on a clustered index) when the number of duplicate key exceptions is sufficiently small.
The break-even point is system-dependant, and requires realistic testing to properly evaluate. Try not to make assumptions or judgements in advance. Test each of the robust implementation alternatives carefully, and choose the one that makes most sense in your scenario.
As an example of a reason for a performance surprise, consider that the index insertion point has to be located at some stage, so repeating that operation may be cheaper than expected. When SQL Server can use the "rowset sharing" optimization, there is even less overhead. The engine locates the insertion point once when checking for existence, and keeps that direct reference for the insert operation.
Though not directly relevant to the question, I should mention some caveats to using IGNORE_DUP_KEY:
- It is not respected by the insert activity of
MERGEstatements. - It cannot be added to an existing index.
- It is not as 'discoverable' as an explicit existence test.
I generally prefer separate statements to MERGE, but you should test it for your intended usage. MERGE can combine an insert and update into a single statement, while taking advantage of the hole-filling optimization and rowset sharing.