Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

IGNORE_DUP_KEY Expand / Collapse
Author
Message
Posted Monday, January 26, 2009 2:44 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 5:50 PM
Points: 7,924, Visits: 9,649
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
Post #643711
Posted Monday, January 26, 2009 3:05 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 1:54 PM
Points: 6,130, Visits: 8,394
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 :D



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #643730
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse