November 4, 2008 at 9:50 pm
Comments posted to this topic are about the item IGNORE_DUP_KEY
November 5, 2008 at 5:16 am
The negative ("This option may NOT be specified...") - urrgh... semantics. Does it mean that it can be but that you shouldn't because it doesn't make sense but it is allowed anyway?
To my surprise, the syntax for CREATE TABLE and ALTER TABLE does allow you to specify the IGNORE_DUP_KEY option for PRIMARY KEY and UNIQUE constraints.
Just the same Hugo - good question. It looks closely at an issue that is odd at best. Thanks
Jamie
November 5, 2008 at 6:28 am
Jamie Longstreet (11/5/2008)
Hi Jamie,
You're right. Looking back at the question, I think it would have been better to phrase it as "... may be specified ..." (and invert the correctness mark of this answer, of course), or possibly as "... can't be specified ...".
To clarify, my intention was solely to test for allowed syntax, not for good or bad practice - though I personally consider this option to be bad practice in its entirety 😉
Just the same Hugo - good question. It looks closely at an issue that is odd at best. Thanks
You're welcome, and thanks for the kind words. 😀
November 5, 2008 at 12:10 pm
CREATE INDEX NotAllowed
ON Dest (Value)
WITH(IGNORE_DUP_KEY = ON);
go
Msg 1916, Level 16, State 4, Line 1
CREATE INDEX options nonunique and ignore_dup_key are mutually exclusive.
ignore_dup_key should come with Unique key.
CREATE UNIQUE INDEX NotAllowed
Good question though. I used this option mostly while merging data from different server.tables into my reporting server, where it won't insert duplicate data.
SQL DBA.
November 5, 2008 at 12:12 pm
Got answer right by fluke. Wasn't sure if I should select his option or not. Though I thought this should be in.
-- This option may not be specified for the index that supports the PRIMARY KEY.
SQL DBA.
November 5, 2008 at 1:29 pm
SanjayAttray (11/5/2008)
CREATE INDEX NotAllowedON Dest (Value)
WITH(IGNORE_DUP_KEY = ON);
go
Msg 1916, Level 16, State 4, Line 1
CREATE INDEX options nonunique and ignore_dup_key are mutually exclusive.
ignore_dup_key should come with Unique key.
CREATE UNIQUE INDEX NotAllowed
Hi Sanjay,
Maybe I am misunderstanding you - isn't this exactly as I say in the answer explanation?
Good question though. I used this option mostly while merging data from different server.tables into my reporting server, where it won't insert duplicate data.
Thanks for the kind words. 🙂
Situations such as what you describe, also known as staging tables, are indeed about the only place where I would consider using this option (though even in that case, I would probably prefer a DISTINCT and/or NOT EXISTS to prevent duplicates - unless that would cause too many performance problems).
November 6, 2008 at 1:06 am
Good Questions Hugo...
November 6, 2008 at 1:39 am
Anirban Paul (11/6/2008)
Good Questions Hugo...
Thanks, Anirban 😉
November 6, 2008 at 8:51 am
Good question. Not because of the question itself but because of one of the answers. I went on a tangent to relearn the goals of ACID. That reminded me of a design issue I'm currently working on. It's amazing how a question like this will have relevance when you least expect it.
Very timely.
Thanks.
🙂
November 6, 2008 at 2:34 pm
skjoldtc (11/6/2008)
Good question. Not because of the question itself but because of one of the answers. I went on a tangent to relearn the goals of ACID. That reminded me of a design issue I'm currently working on. It's amazing how a question like this will have relevance when you least expect it.Very timely.
Thanks.
🙂
You're most welcome. 😉 (Though in all honesty, I can't take any credit for the timing :D)
January 26, 2009 at 2:44 pm
For SQL 2000 (and the question explicitly includes "before SQL 2005") there is no way of specifying ignore_dup_key for a primary key. Any attempt to specify "with ignore_dup_key" in an alter table or create table statement gives the error "ignore_dup_key is not a recognized CREATE TABLE option" (or substitute ALTER for CREATE in that error message); creating a unique index with the option on the primary key columns and then adding the primary key constraint simply results in two indexes (and the one supporting the primary key doesn't have the option) unless the unique index has the name (whether default or non-default) used to attempt to create the constraint in which case the constraint is not created (error message says the named index already exists). So for SQL 2000 at least this part of the answer is wrong. And since this means that there can be no damage to the properties of the primary key the claim that the option leads to ANSI non-conformance on that ground also fails for SQL 2000.
The nondeterminacy caused by the indeterminacy of order of insertion of multiple rows in a single insert statement exists already without the ignore_dup_key, because (for example) identity columns will contain values which depend on the order. Select top n (without an order by clause) provides another example of nondeterminacy. It is therefor misleading to say that SQL Server's nondeterminacy is caused by the ignore_dup_key option. This is true in SQL 2005 and SQL 2008 as well as in SQL 2000. I wouldn't go so far as to say this part of the answer is wrong though: just that it risks engendering a mistaken belief that without this option SQL Server queries would be determinate.
I could make an argument about the ACID properties too (along the lines of climing that insert means something a little different when the target table has a unique index with ignore_dup_key - the statement has done all that was expected of it, so what is incomplete?) but that isn't a useful or interesting argument (ibuilding pitfalls into a programming language is a mistake so I won't go for that one).
Tom
Tom
January 26, 2009 at 3:05 pm
Hi Tom,
Thanks for your elaborate post. You raise some very interesting points, and I'll address them one at a time.
For SQL 2000 (and the question explicitly includes "before SQL 2005") there is no way of specifying ignore_dup_key for a primary key. (...) So for SQL 2000 at least this part of the answer is wrong.
You are right. My bad. I did all the research for the question on SQL Server 2005. And just before posting, I found out that the syntax of the index option had changed, so I went for completeness by including the older syntax as an afterthought. But I failed to check if all answers were valid on SQL2000 and before as well, which I should have. I'm sorry.
And since this means that there can be no damage to the properties of the primary key the claim that the option leads to ANSI non-conformance on that ground also fails for SQL 2000.
Fortunately, I claim no violation of the standard. Indexes are excluded from the standard and can thus never violate the standard; the option to ignore duplicate keys on primary keys is an extension of the standard syntax and therefor not a violation either. For the standards to be violated, you must either implement some syntax from the standard but have it behave differently (as SQL Server does with UNIQUE constraints), or not implement a required feature at all.
The nondeterminacy caused by the indeterminacy of order of insertion of multiple rows in a single insert statement exists already without the ignore_dup_key, because (for example) identity columns will contain values which depend on the order. Select top n (without an order by clause) provides another example of nondeterminacy. It is therefor misleading to say that SQL Server's nondeterminacy is caused by the ignore_dup_key option. This is true in SQL 2005 and SQL 2008 as well as in SQL 2000. I wouldn't go so far as to say this part of the answer is wrong though: just that it risks engendering a mistaken belief that without this option SQL Server queries would be determinate.
Absolutely true. However, the answers to the QotD do not claim that SQL Server's nondeterminacy is caused by this option, but opnly that using this option can cause nondeterministic results. There are indeed lots of other options in SQL Server that also have the ability to cause nondeterminism.
I could make an argument about the ACID properties too (along the lines of climing that insert means something a little different when the target table has a unique index with ignore_dup_key - the statement has done all that was expected of it, so what is incomplete?) but that isn't a useful or interesting argument (ibuilding pitfalls into a programming language is a mistake so I won't go for that one).
I think it would be a very far-fetched argument as well, so I'm glad you didn't make it 😀
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply