SQL Server中的IGNORE_DUP_KEY选项

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

我在MSDN和Google上做了很多搜索,但看起来IGNORE_DUP_KEY选项的描述非常有限。

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)?

   IGNORE_DUP_KEY选项是列的选项吗?一张桌子?几列?索引(使索引唯一)?

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?

   如果将IGNORE_DUP_KEY设置为Yes,则在插入一批具有重复键的记录(使用批量插入WriteToServer ADO.Net函数)时(例如,我插入一些已存在于数据库中的值),SQL Server不会抛出错误。批处理作业将成功完成,但不会插入重复的行。将插入所有其他行,SQL Server将其视为作业成功。我的理解是否正确?

thanks in advance, George

乔治,提前谢谢

2 个解决方案

#1

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

IGNORE_DUP_KEY是CREATE INDEX的一个选项,仅影响多行的插入:

IGNORE_DUP_KEY = ON

IGNORE_DUP_KEY = ON

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

插入所有唯一行,发出警告,并且不插入重复行

IGNORE_DUP_KEY = OFF

IGNORE_DUP_KEY = OFF

• an error is issued and no rows are inserted

发出错误,不插入任何行

#2

1. IGNORE_DUP_KEY is for a given UNIQUE INDEX.

   IGNORE_DUP_KEY用于给定的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.

   “取消后,任何当时处于活动状态的交易都可能继续,就好像更新或插入从未发生过一样.Nonduplicate键正常插入。”所以,是的,批量插入将报告成功。