


In a multirow 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.)
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)