IGNORE_DUP_KEY fails to work as it should, ignoring too many rows - by Steve Kass

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 342100 Comments
Status Closed Workarounds
Type Bug Repros 1
Opened 5/6/2008 5:59:10 AM
Access Restriction Public


In a multi-row insert, when there are two indexes with IGNORE_DUP_KEY, rows that should be inserted are ignored and not inserted.

(Note: I'm posting this on behalf of Alessandro Dereani, who saw Giorgio Rancati post about it in microsoft.public.it.sqlserver.)
Sign in to post a comment.
Posted by Microsoft on 5/6/2008 at 2:57 PM
This is NOT a bug.

SQL SqlServer can't insert a row in a index, then retract that row if a duplicate is detected in another index. So when there are more than two Unique contrains with Ignore_Dup_Key, input rows are first examined for duplicates. The result depends on input order and other undeterministic factors in SORT.

The following steps take place when INSERT statement is executed, assuming the table is empty before inserting.

First, result of union all is SORTed by C1, and the first row in each distinct C1 value is output for furthure process.
Then, the output of first step is SORTed by C2. Again, the first row in each distinct C2 value is output for insert into table.

Several aspects introduce undeterminism: result of the union all is a a set, sqlserver doesn't guarantee order. secondly, the two SORT in the two steps described above doesn't guarantee order of row with same sort column value. Thereforce, the final result, i.e. which rows are inserted, is undertermistic.

For exmaple:
If the input rows are (1,1), (1,2), (2,1), (2,2)
After first step, checking for dups in C1, the result becomes: (1,1), (2,1)
After second step, checking for dups in C2, the result: (1,1)
And this is the row Inserted into the table.

If the input rows are (1,1), (1,2), (2,2), (2,1)
After first step, (1,1), (2,2)
after second step: (1,1), (2,2)