June 8, 2012 at 2:30 am
Ah yes, good point. I think that would have been a better way to put the question.
John
June 8, 2012 at 2:34 am
John Mitchell-245523 (6/8/2012)
IgorMi (6/8/2012)
Try with XACT_ABORT = OFF and you'll see the same results as it is ON. In this case XACT_ABORT does not have impact of the inserted rows. However on the messages, yes it has.IgorMi
True again, but you did not create a unique constraint, so there is nothing to fail. Therefore four rows are returned whether the setting is ON or OFF.
John
Hi
I missed it intentionally. I just tried with IGNORE_DUP_KEY=OFF and the result is same.
IgorMi
Igor Micev,My blog: www.igormicev.com
June 8, 2012 at 2:35 am
John Mitchell-245523 (6/8/2012)
IgorMi (6/8/2012)
Try with XACT_ABORT = OFF and you'll see the same results as it is ON. In this case XACT_ABORT does not have impact of the inserted rows. However on the messages, yes it has.IgorMi
True again, but you did not create a unique constraint, so there is nothing to fail. Therefore four rows are returned whether the setting is ON or OFF.
John
Hi
I missed it intentionally. I just tried with IGNORE_DUP_KEY=OFF and the result is same.
IgorMi
Igor Micev,My blog: www.igormicev.com
June 8, 2012 at 2:55 am
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'.
June 8, 2012 at 3:27 am
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 4Cannot 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
June 8, 2012 at 3:31 am
Your second table is qotd5, but you're still creating the unique index on qotd4--hence it's not too surprising you get all four results the second time, because there's no unique constraint on the qotd5 table!
June 8, 2012 at 3:31 am
Your second example creates and index on qotd4, not qotd5.
June 8, 2012 at 3:34 am
Nils Gustav Stråbø (6/8/2012)
Your second example creates and index on qotd4, not qotd5.
Oh!, Thanks
Yes, you're right definitively.
Thank you again
IgorMi
Igor Micev,My blog: www.igormicev.com
June 8, 2012 at 9:09 am
Guys,
Just my $0.02. From BOL (http://http://msdn.microsoft.com/en-us/library/ms188783.aspx):
IGNORE_DUP_KEY = { ON | OFF }
Specifies the error response when an insert operation attempts to insert duplicate key values into a unique index. The IGNORE_DUP_KEY option applies only to insert operations after the index is created or rebuilt. The option has no effect when executing CREATE INDEX, ALTER INDEX, or UPDATE. The default is OFF.
ON
A warning message will occur when duplicate key values are inserted into a unique index. Only the rows violating the uniqueness constraint will fail.
OFF
An error message will occur when duplicate key values are inserted into a unique index. The entire INSERT operation will be rolled back.
Note the last sentence. If you try the following code:
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)
select 1,'W','Some' union
select 2,'Y','Some' union
select 1,'X','Some' union
select 3,'Z','Some'
commit tran
go
select col2 from qotd4 order by col2
go
drop table qotd4
----------------------------
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)
select 1,'W','Some' union
select 2,'Y','Some' union
select 1,'X','Some' union
select 3,'Z','Some'
commit tran
go
select col2 from qotd4 order by col2
go
drop table qotd4
Unlike the QOTD example that uses individual INSERTs for each row, here the second block of code fails to insert a row, while the first block still inserts 3 rows issuing a "Duplicate key was ignored." message.
I hope this helps.
"El" Jerry.
June 8, 2012 at 9:16 am
IgorMi (6/8/2012)
Nils Gustav Stråbø (6/8/2012)
Thanks for the question.The result of the final SELECT statement would have been the same even if you had dropped the IGNORE_DUP_KEY=ON. The only difference is that it would have raised an error (Cannot insert duplicate key row...), but that wouldn't have aborted the transaction since XACT_ABORT is OFF (by default).
So in my opinion the explanation is a little bit wrong. It is not the IGNORE_DUP_KEY that causes three rows to be returned, but the fact that a run time error does not cause the transaction (some do, but not a duplicate key error) to rollback as long as XACT_ABORT is OFF.
Reference:
http://msdn.microsoft.com/en-us/library/ms188792.aspx
When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing. Depending upon the severity of the error, the entire transaction may be rolled back even when SET XACT_ABORT is OFF. OFF is the default setting.
I don't agree with you.
Ensured with a direct try.
You can execute the following code and ensure yourself that the IGNORE_DUP_KEY = ON has done its effect.
create table qotd5
(
col1 int,
col2 char(1) not null,
col3 varchar(20))
set xact_abort on
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
Regards
IgorMi
You are missing the unique index on your table to actually test what John is saying. Hes point is: it doesnt matter if you turned IGNORE_DUP_KEY on you would get the same values inserted unless you set xact_abort on.
June 8, 2012 at 9:20 am
A nice one to finish my week. Thanks, Ron!
June 8, 2012 at 9:21 am
good question Ron - cheers.
for me, i think that it is important to understand the difference between a warning and an error. If, for example, I use a TRY CATCH for error handling and have XACT_ABORT = OFF, then a warning would allow for the statement block to complete successfully, however, an error would result in the CATCH block determining how deal with the situation.
happy Friday and bring on the weekend!
June 8, 2012 at 11:16 am
Can someone explain to me what purpose is served by ignoring duplicate keys?
In an ideal world there would be data uniquely related to the key in such a way that a duplication of the key would imply a duplication of the remaining fields on the row related to that key - so ignoring a literally duplicated row might make sense.
I don't know that I've ever seen that pattern in real life though. I imagine the duplicate key would likely have fields with different values than are already in the table. Silently discarding values offends my sense of "data integrity."
Under what conditions is it acceptable to attempt an insert but not care whether it happens?
June 8, 2012 at 11:19 am
Good question.
I haven't a clue which button I pressed, but in wasn't the one I thought I pressed. Finger trouble -> wrong answer.
Tom
June 8, 2012 at 12:02 pm
Mike Dougherty-384281 (6/8/2012)
Can someone explain to me what purpose is served by ignoring duplicate keys?...Under what conditions is it acceptable to attempt an insert but not care whether it happens?
good question Mike - I'm curious to hear if anyone uses this in a production realm also. when it comes to data, I guess I am a control freak and i don't like the idea of blindly ignoring attempted inserts.
Viewing 15 posts - 16 through 30 (of 36 total)
You must be logged in to reply to this topic. Login to reply