IGNORE_DUP_KEY option in SQL Server

Question

Hello everyone,

I did quite some search in MSDN and Google, but looks like the description for IGNORE_DUP_KEY option is very limited.

My confusions,

1. Is IGNORE_DUP_KEY option an option for a column? for a table? for a couple of columns? for an index (making index unique)?

2. If set IGNORE_DUP_KEY to Yes, when insert a batch of records (using bulk insert WriteToServer ADO.Net function) with duplicate keys (for example, I insert some values which already exist in database), SQL Server will not throw an error. The batch job will be completed successfully but the duplicated rows will not be inserted. All other rows will be inserted and SQL Server treat it as a job success. Is my understanding correct?

thanks in advance, George

Answer 1

1. IGNORE_DUP_KEY is for a given UNIQUE INDEX.

2. "After the cancellation, any transaction that was active at the time may continue as though the update or insert had never taken place. Nonduplicate keys are inserted normally." So yes, bulk inserts will report success.

Answer 2

IGNORE_DUP_KEY is an option of CREATE INDEX and only affects inserts of multiple rows:

IGNORE_DUP_KEY = ON

• all unique rows get inserted, a warning is issued, and the duplicate rows are not inserted

IGNORE_DUP_KEY = OFF

• an error is issued and no rows are inserted