paul.knibbs (6/8/2012)

---

Looking at the answers, I guess a lot of people think IGNORE_DUP_KEY means it'll allow duplicates to be inserted--which, to be fair, actually makes sense from a strict English point of view!

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.

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

IgorMi (6/8/2012)

---

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