SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by bitbucket / Transactions 4 / Latest Posts

RE: Transactions 4

bitbucket-25253 — Sat, 06 Oct 2012 10:14:33 GMT

[quote][b]kapil190588 (10/6/2012)[/b][hr]When 'x' is getting inserted with WITh(ignore_dup_key=ON) then in select statement why 'x' is not getting returned?[/quote]Read carefully from the cited reference:[quote]When this option is in effect,[b]duplicates are merely discarded[/b][/quote]Now look carefully at the data to be inserted. The 3rd insert statement would if it could, insert a duplicate value of (1) in Column (Col1). Because of this the statement is not executed, that is it is[b] ignored entirely[/b], hence the value of 'X' is not inserted.Hope this helps.

RE: Transactions 4

kapil_kk — Sat, 06 Oct 2012 07:47:51 GMT

When 'x' is getting inserted with WITh(ignore_dup_key=ON) then in select statement why 'x' is not getting returned?

RE: Transactions 4

Neha05 — Thu, 09 Aug 2012 08:41:30 GMT

Nice question, thanks!

RE: Transactions 4

kapfundestanley — Tue, 19 Jun 2012 01:15:56 GMT

Interesting question really learnt something,thank you.

RE: Transactions 4

Olga B — Mon, 11 Jun 2012 18:02:05 GMT

[quote][b]Mike Dougherty[/b][hr]Under what conditions is it acceptable to attempt an insert but not care whether it happens?[/quote]I was wondering the same thing.BOL says:[quote]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.[/quote]So this option allows the insert command to go through, but does not allow changing an existing column to a duplicate value. I am still stumped to think of when it would be a good idea to just lose records on insert. Unless the unique key includes ever column, which allows dropping duplicates on import?

RE: Transactions 4

Koen Verbeeck — Sun, 10 Jun 2012 23:19:40 GMT

Nice question and interesting discussion. Thanks.

RE: Transactions 4

L' Eomot Inversé — Fri, 08 Jun 2012 17:58:52 GMT

[quote][b]OzYbOi d(-_-)b (6/8/2012)[/b][hr][quote][b]Mike Dougherty-384281 (6/8/2012)[/b][hr]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?[/quote]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.[/quote]Suppose someone wants to insert the new stuff in some bunch, without having to work out whether it's new or not? Is it a bad thing or a good thing that the system can do this automatically for him? Of course in a trivial case like that given in the example the code to do this explicitly is itself trivial; in cases where the unique constraint is on a combination of several columns it's still logically trivial, but physically it may be non-trivial, simply because the required where clause is longer so there's more scope for coding error.

RE: Transactions 4

OzYbOi d(-_-)b — Fri, 08 Jun 2012 12:02:17 GMT

[quote][b]Mike Dougherty-384281 (6/8/2012)[/b][hr]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?[/quote]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.

RE: Transactions 4

L' Eomot Inversé — Fri, 08 Jun 2012 11:19:59 GMT

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.

RE: Transactions 4

Mike Dougherty-384281 — Fri, 08 Jun 2012 11:16:44 GMT

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?

RE: Transactions 4

OzYbOi d(-_-)b — Fri, 08 Jun 2012 09:21:01 GMT

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!

RE: Transactions 4

Revenant — Fri, 08 Jun 2012 09:20:31 GMT

A nice one to finish my week. Thanks, Ron!

RE: Transactions 4

Alexander-449406 — Fri, 08 Jun 2012 09:16:16 GMT

[quote][b]IgorMi (6/8/2012)[/b][hr][quote][b]Nils Gustav Stråbø (6/8/2012)[/b][hr]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: [url=http://msdn.microsoft.com/en-us/library/ms188792.aspx]http://msdn.microsoft.com/en-us/library/ms188792.aspx[/url][quote]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.[/quote][/quote]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 onBegin transactioninsert 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 transactionselect col2 from qotd5 order by col2RegardsIgorMi[/quote]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.

RE: Transactions 4

EL Jerry — Fri, 08 Jun 2012 09:09:17 GMT

Guys, Just my $0.02. From BOL ([url]http://http://msdn.microsoft.com/en-us/library/ms188783.aspx[/url]):[quote]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. [b]The entire INSERT operation will be rolled back.[/b][/quote]Note the last sentence. If you try the following code:[code="sql"]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)gobegin traninsert into qotd4(col1,col2,col3)select 1,'W','Some' unionselect 2,'Y','Some' unionselect 1,'X','Some' unionselect 3,'Z','Some'commit trangoselect col2 from qotd4 order by col2godrop 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)gobegin traninsert into qotd4(col1,col2,col3)select 1,'W','Some' unionselect 2,'Y','Some' unionselect 1,'X','Some' unionselect 3,'Z','Some'commit trangoselect col2 from qotd4 order by col2godrop table qotd4[/code]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.

RE: Transactions 4

IgorMi — Fri, 08 Jun 2012 03:34:56 GMT

[quote][b]Nils Gustav Stråbø (6/8/2012)[/b][hr]Your second example creates and index on qotd4, not qotd5.[/quote]Oh!, ThanksYes, you're right definitively.Thank you againIgorMi

RE: Transactions 4

Nils Gustav Stråbø — Fri, 08 Jun 2012 03:31:35 GMT

Your second example creates and index on qotd4, not qotd5.

RE: Transactions 4

paul.knibbs — Fri, 08 Jun 2012 03:31:03 GMT

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!

RE: Transactions 4

IgorMi — Fri, 08 Jun 2012 03:27:19 GMT

[quote][b]Nils Gustav Stråbø (6/8/2012)[/b][hr][quote][b]IgorMi (6/8/2012)[/b]HiI missed it intentionally. I just tried with IGNORE_DUP_KEY=OFF and the result is same.IgorMi[/quote]Let's end the discussion once and for all :-)The original question with ignore_dup_key=on:[code="sql"]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)gobegin traninsert 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 trangoselect col2 from qotd4 order by col2godrop table qotd4[/code]Returns three rows: W,Y,ZThe same statements, but this time with ignore_dup_key=off:[code="sql"]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)gobegin traninsert 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 trangoselect col2 from qotd4 order by col2godrop table qotd4[/code]Returns three rows: W,Y,ZIn addition, SQL server throws an exception, but this does not affect tha fact that three rows are returned.[code="sql"]Msg 2601, Level 14, State 1, Line 4Cannot insert duplicate key row in object 'dbo.qotd4' with unique index 'q04_index'.[/code][/quote]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 returnedWhen IGNORE_DUP_KEY = OFF four rows are returnedHere is my code for the both cases--1create 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 transactioninsert 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 transactionselect col2 from qotd4 order by col2--2create 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 qotd5Begin transactioninsert 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 transactionselect col2 from qotd5 order by col2The ouput 1:wyzThe ouput 2:wxyzRegardsIgorMi

RE: Transactions 4

Nils Gustav Stråbø — Fri, 08 Jun 2012 02:55:09 GMT

[quote][b]IgorMi (6/8/2012)[/b]HiI missed it intentionally. I just tried with IGNORE_DUP_KEY=OFF and the result is same.IgorMi[/quote]Let's end the discussion once and for all :-)The original question with ignore_dup_key=on:[code="sql"]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)gobegin traninsert 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 trangoselect col2 from qotd4 order by col2godrop table qotd4[/code]Returns three rows: W,Y,ZThe same statements, but this time with ignore_dup_key=off:[code="sql"]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)gobegin traninsert 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 trangoselect col2 from qotd4 order by col2godrop table qotd4[/code]Returns three rows: W,Y,ZIn addition, SQL server throws an exception, but this does not affect tha fact that three rows are returned.[code="sql"]Msg 2601, Level 14, State 1, Line 4Cannot insert duplicate key row in object 'dbo.qotd4' with unique index 'q04_index'.[/code]

RE: Transactions 4

IgorMi — Fri, 08 Jun 2012 02:35:34 GMT

[quote][b]John Mitchell-245523 (6/8/2012)[/b][hr][quote][b]IgorMi (6/8/2012)[/b][hr]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[/quote]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[/quote]HiI missed it intentionally. I just tried with IGNORE_DUP_KEY=OFF and the result is same.IgorMi

RE: Transactions 4

IgorMi — Fri, 08 Jun 2012 02:34:35 GMT

RE: Transactions 4

John Mitchell-245523 — Fri, 08 Jun 2012 02:30:25 GMT

Ah yes, good point. I think that would have been a better way to put the question.John

RE: Transactions 4

Mighty — Fri, 08 Jun 2012 02:24:39 GMT

[quote][b]John Mitchell-245523 (6/8/2012)[/b][hr]I got this right, but I'm still a little confused. Am I correct in thinking that what is being tested here is knowledge of the IGNORE_DUP_KEY setting and not anything to do with transactions? Since IGNORE_DUP_KEY means that there is no error, it doesn't matter whether the statements are wrapped in a transaction or not. Furthermore, as we saw in previous questions in this series, the transaction would commit regardless of a unique key violation, unless XACT_ABORT is set to OFF, which is not the default.JohnEdit: didn't mean to parrot what was written in the previous few posts - they weren't there when I started writing this one![/quote]John, the difference is visible when you try to insert all those rows in one transaction like[code="plain"]INSERT INTO [QOTD4] ([col1], [col2], [col3])SELECT 1, 'w', 'something'UNION ALLSELECT 2, 'x', 'or other'UNION ALLSELECT 1, 'y', 'thing'UNION ALLSELECT 3, 'z', 'or what'[/code]With [b]IGNORE_DUP_KEY = OFF [/b] the result would be that there are 0 records inserted. When IGNORE_DUP_KEY = ON it results in 3 records being inserted.

RE: Transactions 4

Nils Gustav Stråbø — Fri, 08 Jun 2012 02:16:53 GMT

IgorMi, you have forgotten to add the unique index in your customized example.And yes, I have tried it. There is a huge difference between XACT_ABORT ON or OFF, and that is 0 rows versurs 3 rows (when you have the unique index in place)

RE: Transactions 4

John Mitchell-245523 — Fri, 08 Jun 2012 02:16:25 GMT

[quote][b]IgorMi (6/8/2012)[/b][hr]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[/quote]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

RE: Transactions 4

Nils Gustav Stråbø — Fri, 08 Jun 2012 02:12:42 GMT

IgorMi, you added SET XACT_ABORT ON, and if you read my answer you'll see that I have explained about this difference. XACT_ABORT was not set in the question, and I assume it therefore will be OFF.

RE: Transactions 4

IgorMi — Fri, 08 Jun 2012 02:11:35 GMT

[quote][b]John Mitchell-245523 (6/8/2012)[/b][hr][quote][b]IgorMi (6/8/2012)[/b][hr]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 onBegin transactioninsert 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 transactionselect col2 from qotd5 order by col2RegardsIgorMi[/quote]Yes, but XACT_ABORT is not ON by default. Since it wasn't explicitly set in the question, we have to assume it's OFF.John[/quote]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

RE: Transactions 4

John Mitchell-245523 — Fri, 08 Jun 2012 02:07:58 GMT

[quote][b]IgorMi (6/8/2012)[/b][hr]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 onBegin transactioninsert 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 transactionselect col2 from qotd5 order by col2RegardsIgorMi[/quote]Yes, but XACT_ABORT is not ON by default. Since it wasn't explicitly set in the question, we have to assume it's OFF.John

RE: Transactions 4

IgorMi — Fri, 08 Jun 2012 02:04:35 GMT

[quote][b]Nils Gustav Stråbø (6/8/2012)[/b][hr]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: [url=http://msdn.microsoft.com/en-us/library/ms188792.aspx]http://msdn.microsoft.com/en-us/library/ms188792.aspx[/url][quote]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.[/quote][/quote]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 onBegin transactioninsert 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 transactionselect col2 from qotd5 order by col2RegardsIgorMi

RE: Transactions 4

John Mitchell-245523 — Fri, 08 Jun 2012 02:00:02 GMT

I got this right, but I'm still a little confused. Am I correct in thinking that what is being tested here is knowledge of the IGNORE_DUP_KEY setting and not anything to do with transactions? Since IGNORE_DUP_KEY means that there is no error, it doesn't matter whether the statements are wrapped in a transaction or not. Furthermore, as we saw in previous questions in this series, the transaction would commit regardless of a unique key violation, unless XACT_ABORT is set to OFF, which is not the default.JohnEdit: didn't mean to parrot what was written in the previous few posts - they weren't there when I started writing this one!

RE: Transactions 4

Stewart "Arturius" Campbell — Fri, 08 Jun 2012 01:53:43 GMT

Interesting question, Ron. thanksHave never used the IGNORE_DUP_KEY option before, so learned something new.

RE: Transactions 4

Mighty — Fri, 08 Jun 2012 01:53:08 GMT

I would like to point out the fact that the select statement would have returned the same results, even if the [b]WITH (IGNORE_DUP_KEY = ON)[/b] would not have been specified with the creation of the index.This is correctly described in the explanation, but some people might overlook the fact, that in this case, only the third INSERT statement fails but not is being rolled back, so not everything in between the BEGIN TRANSACTION...COMMIT TRANSACTION.

RE: Transactions 4

Nils Gustav Stråbø — Fri, 08 Jun 2012 01:52:40 GMT

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: [url=http://msdn.microsoft.com/en-us/library/ms188792.aspx]http://msdn.microsoft.com/en-us/library/ms188792.aspx[/url][quote]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.[/quote]

RE: Transactions 4

IgorMi — Fri, 08 Jun 2012 01:45:37 GMT

[quote][b]paul.knibbs (6/8/2012)[/b][hr]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![/quote]Nice question!Yes, I agree. That option makes commands violating the constraint skipped.RegardsIgorMi

RE: Transactions 4

paul.knibbs — Fri, 08 Jun 2012 01:10:11 GMT

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!

RE: Transactions 4

mohammed moinudheen — Thu, 07 Jun 2012 22:07:14 GMT

Thanks for the question. I am glad I got it right :-)Have a happy weekend.

Transactions 4

bitbucket-25253 — Thu, 07 Jun 2012 22:06:32 GMT

Comments posted to this topic are about the item [B]Transactions 4[/B]