Nils Gustav Stråbø (6/8/2012)
IgorMi (6/8/2012)Hi
I missed it intentionally. I just tried with IGNORE_DUP_KEY=OFF and the result is same.
IgorMi
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'.
Hi,
It is not possible the same result to be returned for IGNORE_DUP_KEY = ON and OFF. Are you missing something?
When IGNORE_DUP_KEY = ON three rows are returned
When IGNORE_DUP_KEY = OFF four rows are returned
Here is my code for the both cases
--1
create table qotd4
(
col1 int,
col2 char(1) not null,
col3 varchar(20))
create unique index q4_index on qotd4(col1) with (ignore_dup_key = on)
Begin transaction
insert into qotd4(col1,col2,col3) values(1,'w','some')
insert into qotd4(col1,col2,col3) values(2,'y','or that')
insert into qotd4(col1,col2,col3) values(1,'x','thing')
insert into qotd4(col1,col2,col3) values(3,'z','or what')
Commit transaction
select col2 from qotd4 order by col2
--2
create table qotd5
(
col1 int,
col2 char(1) not null,
col3 varchar(20))
create unique index q5_index on qotd4(col1) with (ignore_dup_key = off)
delete from qotd5
Begin transaction
insert into qotd5(col1,col2,col3) values(1,'w','some')
insert into qotd5(col1,col2,col3) values(2,'y','or that')
insert into qotd5(col1,col2,col3) values(1,'x','thing')
insert into qotd5(col1,col2,col3) values(3,'z','or what')
Commit transaction
select col2 from qotd5 order by col2
The ouput 1:
w
y
z
The ouput 2:
w
x
y
z
Regards
IgorMi
Igor Micev,My blog: www.igormicev.com