IGNORE_DUP_KEY = ON basically tells SQL Server to insert non-duplicate rows, but silently ignore any duplicates; the default behavior is to raise an error and abort the entire transaction when there are duplicates in a column that doesn't allow them.
IGNORE_DUP_KEY = ON基本上告诉SQL Server插入非重复行，但是默默地忽略任何重复行;默认行为是引发错误并在不允许它们的列中存在重复时中止整个事务。
I've worked with a ton of data that normally has at least one duplicate when there shouldn't be, so I like to make use of UNIQUE constraints when I know a value shouldn't have dups; however when I try to bulk load data the last thing I want is for it to get 90% done and then suddenly run into a duplicate and error the whole thing out (Yes, I know the obvious solution is to make sure there are no duplicates, but sometimes I'm just handed a spreadsheet filled with data and told to load it ASAP).
我已经处理了大量数据，这些数据通常至少有一个副本，当我不知道时，所以当我知道一个值不应该有重复时，我喜欢使用UNIQUE约束;然而，当我尝试批量加载数据时，我想要的最后一件事就是它完成了90％，然后突然遇到重复并错误地解决了整个事情（是的，我知道显而易见的解决方案是确保没有重复，但有时我只是递给一个充满数据的电子表格并告诉他尽快加载它。
So, what is the reason for having the default be OFF, and why wouldn't you want it to be on all the time so that any non-dup entries succeed while you don't have to worry about any duplicates; chances are the duplicates are in there by mistake anyway.
那么，将默认值设置为OFF的原因是什么，以及为什么不希望它一直处于打开状态，以便任何非重复条目成功，而您不必担心任何重复项;无论如何，重复的可能性都在那里。
Is it related to performance, or something else? This seems like a great idea, but there's got to be some reason why it's not the default behavior.
它与性能或其他相关吗？这似乎是一个好主意，但必须有一些理由说明它不是默认行为。
Mainly, is there a good reason not to use this that I should be aware of, or should it be up for evaluating on a case-by-case basis?
主要是，有充分的理由不使用我应该知道的，还是应该根据具体情况进行评估？
5 个解决方案
#1
16
Whenever there is a deviation from the "normal" in the database , you probably want to know about it.
只要数据库中存在与“正常”的偏差，您可能想知道它。
You kept the key unique because of some constraint arising out of business need that dictated it. The database is just keeping up it's side of the deal saying that 'hey you wanted this to be unique but now you are saying something contrary. Make up your mind'
您保持密钥的独特之处在于由于业务需求而产生的一些限制因素。数据库正在跟踪交易的一面，说'嘿，你希望这是独特的，但现在你说的是相反的事情。下定决心'
If that is intentional you can ask database to shut up by using IGNORE_DUP_KEY :)
如果这是故意的，你可以通过使用IGNORE_DUP_KEY来请求数据库关闭:)
#2
2
I guess it might be because the defaults are set to prevent any invalid transactions from failing silently. Everything considered, I'd prefer to choose when to ignore unintended consequences, but please let me know unless I say otherwise.
我想这可能是因为默认设置是为了防止任何无效的事务无声地失败。考虑到一切，我宁愿选择何时忽略意外后果，但除非我另有说明，否则请告诉我。
Example: If I'm depositing my paycheck, I'd like someone to notice if my employer accidentally issued duplicate check numbers.
示例：如果我存入薪水，我希望有人注意到我的雇主是否意外发出了重复的支票号码。
#3
2
chances are the duplicates are in there by mistake anyway.
无论如何，重复的可能性都在那里。
I bet they are! They are bugs. You certainly want to know about them! Turing on IGNORE_DUP_KEY by default is...
我打赌他们是！他们是错误。你当然想知道他们！默认情况下，在IGNORE_DUP_KEY上图灵是......
- hiding bugs...
- 隐藏bug ...
- ...by corrupting data. (Of course the database stays physically consistent, but the data is still wrong from a business logic standpoint.)
- ......破坏数据（当然数据库保持物理上的一致性，但从业务逻辑的角度来看，数据仍然是错误的。）
This is a terrible choice by any standard.
任何标准都是一个糟糕的选择。
Turn it on under special circumstances and then get rid of it as fast as you can so you don't accidentally hide bugs.
在特殊情况下打开它，然后尽快摆脱它，这样你就不会意外地隐藏错误。
#4
1
It can be used as a sanity check. If you know that there should be no conflicts leave it off and it will fail fast on bugs. OTOH for ad-hoc console sessions, I see your point.
它可以用作健全性检查。如果您知道应该没有冲突，请将其关闭，并且它会在错误上快速失败。 OTOH用于ad-hoc控制台会话，我明白你的观点。
#5
1
I'm having a many-to-many relation. I have a product-to-category table with unique index, no other data than prodid and katid in table.
我有一个多对多的关系。我有一个具有唯一索引的产品到类别表，没有其他数据比表中的prodid和katid。
So I'm setting IGNORE_DUP_KEY on the unique (prodid,katid) index.
所以我在唯一（prodid，katid）索引上设置IGNORE_DUP_KEY。
So I can safely say "add product (1,2,3) to category (a,b,c)" without having to check if some products are in some categories already; I only care about the end result.
所以我可以安全地说“将产品（1,2,3）添加到类别（a，b，c）”而不必检查某些产品是否已经在某些类别中;我只关心最终结果。
#1
16
Whenever there is a deviation from the "normal" in the database , you probably want to know about it.
只要数据库中存在与“正常”的偏差，您可能想知道它。
You kept the key unique because of some constraint arising out of business need that dictated it. The database is just keeping up it's side of the deal saying that 'hey you wanted this to be unique but now you are saying something contrary. Make up your mind'
您保持密钥的独特之处在于由于业务需求而产生的一些限制因素。数据库正在跟踪交易的一面，说'嘿，你希望这是独特的，但现在你说的是相反的事情。下定决心'
If that is intentional you can ask database to shut up by using IGNORE_DUP_KEY :)
如果这是故意的，你可以通过使用IGNORE_DUP_KEY来请求数据库关闭:)
#2
2
I guess it might be because the defaults are set to prevent any invalid transactions from failing silently. Everything considered, I'd prefer to choose when to ignore unintended consequences, but please let me know unless I say otherwise.
我想这可能是因为默认设置是为了防止任何无效的事务无声地失败。考虑到一切，我宁愿选择何时忽略意外后果，但除非我另有说明，否则请告诉我。
Example: If I'm depositing my paycheck, I'd like someone to notice if my employer accidentally issued duplicate check numbers.
示例：如果我存入薪水，我希望有人注意到我的雇主是否意外发出了重复的支票号码。
#3
2
chances are the duplicates are in there by mistake anyway.
无论如何，重复的可能性都在那里。
I bet they are! They are bugs. You certainly want to know about them! Turing on IGNORE_DUP_KEY by default is...
我打赌他们是！他们是错误。你当然想知道他们！默认情况下，在IGNORE_DUP_KEY上图灵是......
- hiding bugs...
- 隐藏bug ...
- ...by corrupting data. (Of course the database stays physically consistent, but the data is still wrong from a business logic standpoint.)
- ......破坏数据（当然数据库保持物理上的一致性，但从业务逻辑的角度来看，数据仍然是错误的。）
This is a terrible choice by any standard.
任何标准都是一个糟糕的选择。
Turn it on under special circumstances and then get rid of it as fast as you can so you don't accidentally hide bugs.
在特殊情况下打开它，然后尽快摆脱它，这样你就不会意外地隐藏错误。
#4
1
It can be used as a sanity check. If you know that there should be no conflicts leave it off and it will fail fast on bugs. OTOH for ad-hoc console sessions, I see your point.
它可以用作健全性检查。如果您知道应该没有冲突，请将其关闭，并且它会在错误上快速失败。 OTOH用于ad-hoc控制台会话，我明白你的观点。
#5
1
I'm having a many-to-many relation. I have a product-to-category table with unique index, no other data than prodid and katid in table.
我有一个多对多的关系。我有一个具有唯一索引的产品到类别表，没有其他数据比表中的prodid和katid。
So I'm setting IGNORE_DUP_KEY on the unique (prodid,katid) index.
所以我在唯一（prodid，katid）索引上设置IGNORE_DUP_KEY。
So I can safely say "add product (1,2,3) to category (a,b,c)" without having to check if some products are in some categories already; I only care about the end result.
所以我可以安全地说“将产品（1,2,3）添加到类别（a，b，c）”而不必检查某些产品是否已经在某些类别中;我只关心最终结果。