Home Dashboard Directory Help

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


 as By Design Help for as By Design

Sign in
to vote
Type: Bug
ID: 342100
Opened: 5/6/2008 5:59:10 AM
Access Restriction: Public
User(s) can reproduce this bug


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)
Sign in to post a workaround.