Why would you NOT set IGNORE_DUP_KEY to ON?

Question

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?

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

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

Answer 3

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