IGNORE_DUP_KEY — what it is, since when, and the performance trap

1What it actually is

IGNORE_DUP_KEY is an option on a UNIQUE index (or the unique index behind a PRIMARY KEY / UNIQUE constraint). It changes one thing: how the engine reacts when an INSERT would create a duplicate key.

OFF (the default) — the first duplicate raises an error and the entire statement rolls back. None of the rows land, even if only one was a dupe.
ON — the duplicate rows are silently discarded with a warning, and every non-duplicate row in the same statement still inserts successfully.

You set it when you create the index, not at insert time:

-- modern form (SQL Server 2005+)
CREATE UNIQUE INDEX IX_Order_NaturalKey
  ON dbo.[Order] (CustomerId, ExternalRef)
  WITH (IGNORE_DUP_KEY = ON);

-- on a constraint, same idea
ALTER TABLE dbo.[Order]
  ADD CONSTRAINT UQ_Order UNIQUE (CustomerId, ExternalRef)
  WITH (IGNORE_DUP_KEY = ON);

The idempotency angle With it ON, re-running an insert of rows that already exist becomes a no-op for the dupes instead of an error — so a retried/replayed batch converges to "one row per key" without you writing WHERE NOT EXISTS or catching error 2627. That's the genuinely useful part. The sharp edge is below.

2Since when? (the "is this new" answer)

The behaviour itself is one of the oldest knobs in the product — it shipped in the early SQL Server / Sybase lineage and was already standard by SQL Server 2000. What changed over the years is only the syntax and a 2017 performance escape hatch.

≤ 2000
The option already exists Inherited from the Sybase-era engine. In SQL Server 2000 you wrote the bare form WITH IGNORE_DUP_KEY, and it couldn't be applied to a primary key.
2005
The WITH (option = ON|OFF) syntax arrives SQL Server 2005 introduced the parenthesised index-options grammar — this is where WITH (IGNORE_DUP_KEY = ON) comes from. The old bare WITH IGNORE_DUP_KEY still works and means the same as = ON.
2017
SUPPRESS_MESSAGES = ON added SQL Server 2017 added a sub-option that also fixes the clustered-index performance trap (see §4). Lets you skip the per-dupe warning chatter and force the fast plan.

So if a plan or teammate is presenting WITH (IGNORE_DUP_KEY = ON) as a modern trick, the honest framing is: old feature, two-decade-old syntax. Nothing to wait for or worry about on version grounds.

3What it does — and doesn't — cover

✓ It applies to

INSERT operations after the index is created/rebuilt — the only thing it affects.
UNIQUE indexes, clustered or nonclustered (and the index behind a PK/unique constraint).
Multi-row inserts: good rows in, dupes dropped, with a warning per discarded set.

✗ It does nothing for

UPDATE — an update that would create a duplicate still errors, always. Same for the create/rebuild of the index itself.
Non-unique indexes, indexed views, XML, spatial, and filtered indexes — can't be set to ON.
Letting you build a unique index over data that already has dupes — that's rejected regardless.

Silent by design Discarded rows vanish with only a warning (not an error). Your "inserted 1000 rows" intent can quietly become 970 actually stored, and well-behaved apps won't notice. If "every row must land or I want to know why" matters, that's an argument for explicit dedup logic instead. Check the setting any time via the ignore_dup_key column in sys.indexes.

4The performance trap: clustered vs nonclustered

This is the one genuinely surprising thing about the option. Where the unique index lives changes performance by more than an order of magnitude when duplicates are common. Paul White's benchmark — 1,000,000 rows, only 1,000 distinct values (so ~999k dupes) — makes it vivid:

Nonclustered + IGNORE_DUP_KEYquery processor strips dupes first

700 ms

Baseline (non-unique clustered)no dedup work at all

900 ms

Clustered + IGNORE_DUP_KEYstorage engine, exception per dupe

15,900 ms

Lower is faster. The clustered case is ~18× the nonclustered case here — and ~92% of its runtime is raising and catching internal exceptions, one per discarded duplicate.

Why the gap exists

Aspect	Nonclustered (fast)	Clustered (slow)
Who handles it	The query processor, up front in the plan	The storage engine, row by row
How dupes are removed	A merge semi-join + Assert + Segment/Top eliminate dupes before any insert is attempted	Each row navigates the b-tree, takes latches/locks, builds the row & log record, then hits the dupe and throws
Cost driver	One set-based pass; no exceptions	An exception raised & caught per duplicate — the dominant cost

The asymmetry is architectural: the clustered index is the table, and it's updated before the nonclustered indexes. By the time a nonclustered uniqueness check would fire, the base row already exists — so SQL Server moves that dedup work to the query processor for nonclustered, but the clustered path is stuck doing it the expensive way.

5The 2017 escape hatch & the alternatives

If you're on SQL Server 2017 or later and you do want it on a clustered index, add SUPPRESS_MESSAGES = ON. It silences the per-dupe warnings and makes the optimizer use the fast query-processor plan even for the clustered case:

CREATE UNIQUE CLUSTERED INDEX CIX_Order
  ON dbo.[Order] (CustomerId, ExternalRef)
  WITH (IGNORE_DUP_KEY = ON (SUPPRESS_MESSAGES = ON));

And the honest comparison — when duplicates are the norm, plain set-based dedup often beats IGNORE_DUP_KEY on a clustered index anyway. In the same benchmark, a SELECT DISTINCT before the insert ran in ~400 ms vs the 15,900 ms clustered case.

✓ Reach for IGNORE_DUP_KEY when

The natural key is a nonclustered unique index.
Duplicates are rare (a few collisions in a batch), and you want idempotent "insert what's new".
You're on 2017+ and can add SUPPRESS_MESSAGES = ON for a clustered key.

✗ Prefer explicit dedup when

The unique key is the clustered index on pre-2017 and dupes are common — use INSERT … SELECT DISTINCT or WHERE NOT EXISTS / MERGE.
You need to know how many rows were rejected, not have them disappear silently.
Rejections should trigger app logic (log, alert, dead-letter) rather than a swallowed warning.

6Other interesting bits

It's a documented, supported optimizer trick for "insert only the new rows" — Paul White even has a piece on using it creatively for set-difference style inserts.
Transactional nuance: the discarded duplicates don't roll back the surrounding transaction — the statement succeeds with the survivors, which is exactly why it reads as "idempotent insert".
It's per-index, not per-table: a table can have one unique index with it ON and another with it OFF; the OFF one will still hard-error a dupe even if the first would've ignored it.
Warning, not error: client libraries surface it as an informational message (it won't trip a normal try/catch), which is the trap for code that assumes "no exception = all rows inserted".