为什么不将IGNORE_DUP_KEY设置为ON？ [英] Why would you NOT set IGNORE_DUP_KEY to ON?

问题描述

IGNORE_DUP_KEY = ON 基本上告诉SQL Server插入非重复的行，但是默认地忽略任何重复的行;默认的行为是引发错误，并在列中不存在重复项时中止整个事务。

我已经与一吨数据通常至少有一个副本，当不应该有，所以我喜欢使用 UNIQUE 约束，当我知道一个值不应该有dups;然而，当我尝试批量加载数据时，我想要的最后一件事是让它获得90％的完成，然后突然遇到一个重复的错误（是的，我知道明显的解决方案是确保没有重复的，但有时我只是递交了一个充满数据的电子表格，并且尽快将其加载。

所以默认的原因是 OFF ，为什么不会你希望它一直在，所以任何非重复条目成功，而你不必担心任何重复;机会是重复的错误在那里无论如何。

它与性能有关，还是其他？这似乎是一个好主意，但是必须有一些原因，它不是默认的行为。

主要是有一个很好的理由不使用这个我应该知道的情况，还是应该根据具体情况进行评估？

解决方案

p>每当数据库中与正常有偏差时，您可能想要了解它。

由于由于需要的业务需求而导致的一些约束，您保留了唯一的密钥。数据库只是跟上交易的一面，说你希望这是独一无二的，但现在你在说什么是相反的。弥补你的想法'

如果这是有意的，你可以通过使用IGNORE_DUP_KEY来询问数据库关闭：）

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.

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

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.

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?

解决方案

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设置为ON？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！