Using the IGNORE_DUP_KEY index option

This is a quick demo I put together for a talk on indexes earlier. It shows the behavior of the IGNORE_DUP_KEY option when you create a unique index.
First, create a small sample table. I used tempdb but feel free to use whatever test database you want. Then create a unique clustered index on the field1 column.

CREATE TABLE Table1
(
    field1 int        NOT NULL,
    field2 char(2)    NOT NULL
)
GO

CREATE UNIQUE CLUSTERED INDEX cl_Table_1 ON Table1 
(
    field1 ASC
)
GO

Next insert a few records into the table. I’m inserting records in two separate blocks to trap the intentional duplicate key error.

BEGIN TRY
    INSERT INTO Table1 VALUES(1, 'aa')
    INSERT INTO Table1 VALUES(2, 'ab')
    INSERT INTO Table1 VALUES(3, 'bb')
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE ()
END CATCH

BEGIN TRY
    INSERT INTO Table1 VALUES(4, 'cc')
    INSERT INTO Table1 VALUES(3, 'ac')
    INSERT INTO Table1 VALUES(5, 'dd')
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE ()
END CATCH

SELECT field1, field2 FROM Table1

You won’t get any errors in the first block because all three values are unique as defined by the key. The second block will generate the duplicate key error when it tries to insert the value 3 into field1. At this point the error is trapped and value 5 doesn’t get inserted. Viewing the values shows the 4 records.

field1	fiels2
1	aa
2	ab
3	bb
4	cc

Now lets alter the index to ignore duplicate key errors. When you use this option any error is ignored and execution of the batch continues. Only the duplicate record is not inserted. We’ll insert three more records and again trap any errors.

ALTER INDEX cl_Table_1 ON Table1 SET ( IGNORE_DUP_KEY  = ON );
GO

BEGIN TRY
    INSERT INTO Table1 VALUES(6, 'zz')
    INSERT INTO Table1 VALUES(2, 'dd')
    INSERT INTO Table1 VALUES(7, 'cc')
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE ()
END CATCH

This time no error is generated. The duplicate value of 2 is ignored and execution continues, meaning that the next record of 7 is also inserted. Viewing the values shows this.

field1	field2
1	aa
2	ab
3	bb
4	cc
6	zz
7	cc