Let's end the discussion once and for all 🙂

The original question with ignore_dup_key=on:

create table qotd4(col1 int not null, col2 char(1) not null, col3 varchar(20))

create unique index q04_index on qotd4(col1) with (ignore_dup_key=on)

go

begin tran

insert into qotd4(col1,col2,col3) values(1,'W','Some')

insert into qotd4(col1,col2,col3) values(2,'Y','Some')

insert into qotd4(col1,col2,col3) values(1,'X','Some')

insert into qotd4(col1,col2,col3) values(3,'Z','Some')

commit tran

go

select col2 from qotd4 order by col2

go

drop table qotd4Returns three rows: W,Y,Z

The same statements, but this time with ignore_dup_key=off:

create table qotd4(col1 int not null, col2 char(1) not null, col3 varchar(20))

create unique index q04_index on qotd4(col1) with (ignore_dup_key=off)

go

begin tran

insert into qotd4(col1,col2,col3) values(1,'W','Some')

insert into qotd4(col1,col2,col3) values(2,'Y','Some')

insert into qotd4(col1,col2,col3) values(1,'X','Some')

insert into qotd4(col1,col2,col3) values(3,'Z','Some')

commit tran

go

select col2 from qotd4 order by col2

go

drop table qotd4Returns three rows: W,Y,Z

In addition, SQL server throws an exception, but this does not affect tha fact that three rows are returned.

Msg 2601, Level 14, State 1, Line 4

Cannot insert duplicate key row in object 'dbo.qotd4' with unique index 'q04_index'.