June 8, 2012 at 5:58 pm
OzYbOi d(-_-)b (6/8/2012)
Mike Dougherty-384281 (6/8/2012)
Can someone explain to me what purpose is served by ignoring duplicate keys?...Under what conditions is it acceptable to attempt an insert but not care whether it happens?
good question Mike - I'm curious to hear if anyone uses this in a production realm also. when it comes to data, I guess I am a control freak and i don't like the idea of blindly ignoring attempted inserts.
Suppose someone wants to insert the new stuff in some bunch, without having to work out whether it's new or not? Is it a bad thing or a good thing that the system can do this automatically for him? Of course in a trivial case like that given in the example the code to do this explicitly is itself trivial; in cases where the unique constraint is on a combination of several columns it's still logically trivial, but physically it may be non-trivial, simply because the required where clause is longer so there's more scope for coding error.
Tom
June 10, 2012 at 11:19 pm
Nice question and interesting discussion. Thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 11, 2012 at 6:02 pm
Mike Dougherty
Under what conditions is it acceptable to attempt an insert but not care whether it happens?
I was wondering the same thing.
BOL says:
Specifies the error response when an insert operation attempts to insert duplicate key values into a unique index. The IGNORE_DUP_KEY option applies only to insert operations after the index is created or rebuilt. The option has no effect when executing CREATE INDEX, ALTER INDEX, or UPDATE. The default is OFF.
So this option allows the insert command to go through, but does not allow changing an existing column to a duplicate value.
I am still stumped to think of when it would be a good idea to just lose records on insert. Unless the unique key includes ever column, which allows dropping duplicates on import?
June 19, 2012 at 1:15 am
Interesting question really learnt something,thank you.
“When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris
August 9, 2012 at 8:41 am
Nice question, thanks!
October 6, 2012 at 7:47 am
When 'x' is getting inserted with WITh(ignore_dup_key=ON) then in select statement why 'x' is not getting returned?
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
October 6, 2012 at 10:14 am
kapil190588 (10/6/2012)
When 'x' is getting inserted with WITh(ignore_dup_key=ON) then in select statement why 'x' is not getting returned?
Read carefully from the cited reference:
When this option is in effect,duplicates are merely discarded
Now look carefully at the data to be inserted. The 3rd insert statement would if it could, insert a duplicate value of (1) in Column (Col1). Because of this the statement is not executed, that is it is ignored entirely, hence the value of 'X' is not inserted.
Hope this helps.
Viewing 7 posts - 31 through 36 (of 36 total)
You must be logged in to reply to this topic. Login to reply