• 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 😀


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/