4

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 ON, when I 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?

Charlieface
  • 52,284
  • 6
  • 19
  • 43
George2
  • 44,761
  • 110
  • 317
  • 455
  • see the comment in my answer; The affect of this index option is to allow all unique values to be inserted and ignore (with a warning) the abort failure because there was a duplicate. The dup is not inserted. If you insert a dup it still will not be included in the table (see my first bullet item) – KM. Mar 30 '09 at 13:38

2 Answers2

9

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
KM.
  • 101,727
  • 34
  • 178
  • 212
  • the default is IGNORE_DUP_KEY = OFF – KM. Mar 27 '09 at 14:53
  • @mike, I do not agree with -- "only affects inserts of multiple rows". I think even insert a single row (non bulk-insert), IGNORE_DUP_KEY option still takes effects. Any comments? – George2 Mar 28 '09 at 10:57
  • right from my online help for "CREATE INDEX (Transact-SQL)": IGNORE_DUP_KEY = { ON | OFF } Specifies the error response to duplicate key values in a multiple-row insert operation on a unique clustered or unique nonclustered index. The default is OFF. – KM. Mar 30 '09 at 13:33
  • `create unique clustered index IDX_MY_IDX on MyTable(column1, column2) with IGNORE_DUP_KEY` did the trick for me – Jeffrey Knight Dec 23 '22 at 13:13
3

  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.

Matt Rogish
  • 24,435
  • 11
  • 76
  • 92
  • @Matt, if set IGNORE_DUP_KEY = ON, any ways to know whether a row is inserted or not (I have this concern because my purpose is to insert only non-existing rows, and for existing rows, I want to keep the old value but gets some forms of feedback information)? (continued.) – George2 Mar 28 '09 at 10:59