Quick question regarding ignore_dup_key option:

If the attempted insert includes multiple "duplicates", how do you control which one succeeds.

From trial and error, it looks like if my set of duplicates is ordered, the first row will be successful and the subsequent rows will fail. (See test 2 and test 3 below.) But it would sure be nice if someone could confirm that this is the defined behavior. Anyone? Thanks.

create table ItemTest1

( ItemNum int

,Value varchar(10))

go

create unique index pk_ItemTest1 on ItemTest1(ItemNum) with (IGNORE_DUP_KEY = ON)

go

--test 1

insert into ItemTest1

select 1,'row1'

union all

select 1,'row2'

union all

select 1,'row3'

select * from ItemTest1

where ItemNum=1

--test 2

insert into ItemTest1

select 2,'row1'

union all

select 2,'row2'

union all

select 2,'row3'

order by 2

select * from ItemTest1

where ItemNum=2

--test 3

insert into ItemTest1

select 3,'row1'

union all

select 3,'row2'

union all

select 3,'row3'

order by 2 desc

select * from ItemTest1

where ItemNum=3

drop table ItemTest1