» » Interesting index option IGNORE_DUP_KEY

Interesting index option IGNORE_DUP_KEY

Edit
In one of migration scripts, we needed
to create a table to hold distinct values.
In the population process, there was no
option to specify 'SELECT DISTINCT',
because the population was done by batches.
Instead of checking if a value already exist
in a target table, we created an unique index
with IGNORE_DUP_KEY option ON.


Additional example will be related to ETL process,
For example, if you have different sources of data
Or same row can come more than once from one source,
You can use this index option on ODS/Delta tables.

So, what this option does? When this index option turned off (by default) and
you try to insert duplicate value to a column with uniquie index, you will get an
error something like this:
Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row .........

In case when you create an index(or alter it) with IGNORE_DUP_KEY=ON,
the duplicate values will be not inserted and you will get warning message:
Duplicate key was ignored.

Here the example:
CREATE TABLE TestTbl(Name VARCHAR(10))
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_Name]
ON TestTbl (Name ASC) WITH (IGNORE_DUP_KEY = ON)
GO

INSERT INTO TestTbl(Name)
SELECT 'Dan' UNION ALL
SELECT 'Tim' UNION ALL
SELECT 'Dan'
GO
SELECT * FROM TestTbl

Note: On UPDATE command this feature do NOT work,
you will get an error. So be aware of this option,
Thanks for reading.