CODEWITHSUNDEEP
sql-servert-sqldatabase-design
Wayne Molina
Wayne Molina

Reputation: 19616

Why would you NOT set IGNORE_DUP_KEY to ON?

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?

Upvotes: 10

Views: 14588

Answers (5)

usr
usr

Reputation: 171236

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

  1. hiding bugs...
  2. ...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.

Upvotes: 2

Leif Neland
Leif Neland

Reputation: 1528

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.

So I'm setting IGNORE_DUP_KEY on the unique (prodid,katid) index.

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.

Upvotes: 1

Learning
Learning

Reputation: 8185

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

Upvotes: 17

dkretz
dkretz

Reputation: 37655

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.

Upvotes: 2

BCS
BCS

Reputation: 78673

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.

Upvotes: 1

Related Questions