InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 21:43:34 GMT20http://www.sqlservercentral.com/Forums/Topic563553-1376-1.aspxgood question+3 :-)Thu, 13 Dec 2012 06:48:29 GMTkapil_kkhttp://www.sqlservercentral.com/Forums/Topic563553-1376-1.aspxGood Question But not clear in the sense of Blocking the record.Fri, 24 Oct 2008 03:19:05 GMTshamas-805328http://www.sqlservercentral.com/Forums/Topic563553-1376-1.aspxgood question followed by interesting discussion. :)Mon, 20 Oct 2008 18:35:37 GMTAnam Vermahttp://www.sqlservercentral.com/Forums/Topic563553-1376-1.aspx[quote][b]Ric Sierra (9/4/2008)[/b][hr]I'm not completly agree with the answer, because depends of the context:If you are looking for the best execution plan the answer is #1If you are looking for less deadlock the answer is #2But if the target is INSERT a new record, both solutions works.[/quote]Isn't 'correctness' favoured first ?i.e. you look to ensure that you achieve what was intended and then look to optimise (secondary). The correct thing in my mind would be option 2 as explained in the solution.Let the definition of 'correctness' commence...Wed, 10 Sep 2008 06:51:44 GMTPreet_Shttp://www.sqlservercentral.com/Forums/Topic563553-1376-1.aspxExcellent question...Mon, 08 Sep 2008 23:10:37 GMTAnipaulhttp://www.sqlservercentral.com/Forums/Topic563553-1376-1.aspx[quote][b]Sergiy (9/7/2008)[/b][hr][quote][b]icocks (9/5/2008)[/b][hr]A further thing to bear in mind - if there's an Insert trigger on the table, then it will be fired in response to option 2, but not option 1. Depending on what it does (& how smart you've been in limiting the processing for a zero row insert) then there may be a further performance hit and/or lock escalation to take into account. Of course you may have good reasons for wanting it to fire for even an unsuccessful insert attempt.In general I prefer to anti-join the value list back to the insert table since that's much more easily expandable for multi-row inserts & compound keys.My opinion is that there's rarely a truly right or wrong answer to this kind of question - the real skill lies in understanding the strengths & weaknesses of each approach & then choosing the appropriate option for the system it's being applied to.[/quote]Look on what you are choosing from:1. A solution which may cause errors in production environment but easy on resources;2. A solution which is error safe but potentially causes some overhead.Which one wold you choose for your car?Are those airbags an overhead? Have you ever been in a situation when you really needed it?Have any of you friends?So when you choose a car - will you prefer the one without airbags?Why?[/quote]Having walked away from an accident where I slammed my car into a ditch at 70 MPH, I want both seat belts (lap and shoulder) and air bags.:cool:Mon, 08 Sep 2008 19:03:01 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic563553-1376-1.aspxVery well put on the tree falling in the woods comment, in my experience triggers appear to go by rows affected but also by statements in the execution i.e. if it reads the word "UPDATE" in a piece of code and there is an ON Update trigger on that table...f***en Bang.Mon, 08 Sep 2008 18:53:59 GMTCarlton Leachhttp://www.sqlservercentral.com/Forums/Topic563553-1376-1.aspxI looked at this as more of a which one performs better question rather than the nature of the operation hence I chose 1.I guess the absence of a transactin in the first statement has pulled the wool over everyones eyes!Very good question this one, tip top!Mon, 08 Sep 2008 15:35:43 GMTCarlton Leachhttp://www.sqlservercentral.com/Forums/Topic563553-1376-1.aspxThis is really useful information, thanks guys!Mon, 08 Sep 2008 00:02:06 GMTChristian Buettner-167247http://www.sqlservercentral.com/Forums/Topic563553-1376-1.aspxGreat stuff here everyone. Thanks OP!!!!! :DSun, 07 Sep 2008 19:20:59 GMTeinman33http://www.sqlservercentral.com/Forums/Topic563553-1376-1.aspx[quote][b]icocks (9/5/2008)[/b][hr]A further thing to bear in mind - if there's an Insert trigger on the table, then it will be fired in response to option 2, but not option 1. Depending on what it does (& how smart you've been in limiting the processing for a zero row insert) then there may be a further performance hit and/or lock escalation to take into account. Of course you may have good reasons for wanting it to fire for even an unsuccessful insert attempt.In general I prefer to anti-join the value list back to the insert table since that's much more easily expandable for multi-row inserts & compound keys.My opinion is that there's rarely a truly right or wrong answer to this kind of question - the real skill lies in understanding the strengths & weaknesses of each approach & then choosing the appropriate option for the system it's being applied to.[/quote]Look on what you are choosing from:1. A solution which may cause errors in production environment but easy on resources;2. A solution which is error safe but potentially causes some overhead.Which one wold you choose for your car?Are those airbags an overhead? Have you ever been in a situation when you really needed it?Have any of you friends?So when you choose a car - will you prefer the one without airbags?Why?Sun, 07 Sep 2008 00:43:36 GMTSergiyhttp://www.sqlservercentral.com/Forums/Topic563553-1376-1.aspxI always use IF EXISTS(select 1 from inserted) or deleted.It allows not to run "insert" part of INSERT/UPDATE/DELETE trigger only when there are actually inserted rows.Fri, 05 Sep 2008 17:41:18 GMTSergiyhttp://www.sqlservercentral.com/Forums/Topic563553-1376-1.aspx[quote][b]JohnG (9/5/2008)[/b][hr]Only if you have one and only one trigger in the entire database!The trigger that was fired and thus sets @@ROWCOUNT to zero could be some DML operation in a trigger [u]on another table[/u]. Note that @@ROWCOUNT is global and not scoped to the DML on the table. It is reset on DML within a trigger.[/quote]Hi John,If a DML operation that causes a trigger to fire is started in a trigger, the flow of that trigger is interrupted and the other trigger gets executed first. Once that is finished, the first resumes. So when the second trigger starts, the @@ROWCOUNT will be equal to the number of rows affected by the DML operation in the first trigger that caused the second to fire, as expected.I know this very well, because I have been heavily involved in maintaining a database that had lots of nested (and even recursive) trigger executions.[quote]In my case I had a parent->child relationship (e.g., Master->Detail) that required a trigger on the parent table. The deletions from the child table were done via a CASCADE delete which then fired the required trigger on the child table.So the trigger fired, it done some things, the last of which affected zero rows, then the trigger on the child table fired. Since the last DML operation in the prior trigger resulted in zero rows affected, the 2nd trigger didn't do anything as it erroneously was testing @@ROWCOUNT.This was a bitch to track down as to why the trigger didn't work properly all of the time.I repeat, DO NOT test @@ROWCOUNT at the beginning of a trigger. Properly check the number of rows in the related trigger tables.[/quote]Aaah, cascading deletes/updates. That is indeed a very good point. Yes, if you have those, you should indeed avoid testing @@ROWCOUNT, since the order in which the triggers fire is undefined.So I guess I should back down and reuce my statement to "@@ROWCOUNT is safe if used as the first line of code in the trigger, if there is no more than one trigger for the operation, and if the table the trigger is defined upon is not involved on either side of a foreign key with cascading updates or deletes". (But in those cases, @@ROWCOUNT is better, because it's slightly faster than checking for existance of a row in the trigger tables, and potentially lots faster than counting those rows).Fri, 05 Sep 2008 15:58:59 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic563553-1376-1.aspx[quote]I had completely forgotten about this because I never declare more than one trigger for the same action. Frankly, I never really understood why people want to have multiple triggers for an action; I always code all checks and changes required in a single trigger.(And if you do have a single trigger, then you can safely check @@ROWCOUNT as the first statement in the trigger, as the last statement will THEN always be the statement that caused the trigger to fire.[/quote]Only if you have one and only one trigger in the entire database!The trigger that was fired and thus sets @@ROWCOUNT to zero could be some DML operation in a trigger [u]on another table[/u]. Note that @@ROWCOUNT is global and not scoped to the DML on the table. It is reset on DML within a trigger.In my case I had a parent->child relationship (e.g., Master->Detail) that required a trigger on the parent table. The deletions from the child table were done via a CASCADE delete which then fired the required trigger on the child table.So the trigger fired, it done some things, the last of which affected zero rows, then the trigger on the child table fired. Since the last DML operation in the prior trigger resulted in zero rows affected, the 2nd trigger didn't do anything as it erroneously was testing @@ROWCOUNT.This was a bitch to track down as to why the trigger didn't work properly all of the time.I repeat, DO NOT test @@ROWCOUNT at the beginning of a trigger. Properly check the number of rows in the related trigger tables.Fri, 05 Sep 2008 15:18:14 GMTMauvehttp://www.sqlservercentral.com/Forums/Topic563553-1376-1.aspx[quote][b]JohnG (9/5/2008)[/b][hr][quote][b]Hugo Kornelis (9/5/2008)[/b][hr]That's why many developers make it a habit to start each trigger with this line:[code]IF @@ROWCOUNT = 0 RETURN;[/code]Do make sure that it's the very first line of the trigger, though.[/quote][i][b]DO NOT[/b][/i] use this in a trigger to test if any rows need to be processed by the trigger.Although this technique has been documented in independent books (e.g., Ken Henderson) the value could be zero from some other action performed by some other trigger. I.e., the value of @@ROWCOUNT does not always reflect the number of rows affected in the table that the trigger is attached to.From BOL:[i]Returns the number of rows affected by the [u]last[/u] statement.[/i]Trust me on this as I have had this experience![/quote]Hi John,Good point. Thanks for the warning.I had completely forgotten about this because I never declare more than one trigger for the same action. Frankly, I never really understood why people want to have multiple triggers for an action; I always code all checks and changes required in a single trigger.(And if you do have a single trigger, then you can safely check @@ROWCOUNT as the first statement in the trigger, as the last statement will [b]THEN[/b] always be the statement that caused the trigger to fire.Fri, 05 Sep 2008 14:46:20 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic563553-1376-1.aspx[quote][b]Hugo Kornelis (9/5/2008)[/b][hr]That's why many developers make it a habit to start each trigger with this line:[code]IF @@ROWCOUNT = 0 RETURN;[/code]Do make sure that it's the very first line of the trigger, though.[/quote][i][b]DO NOT[/b][/i] use this in a trigger to test if any rows need to be processed by the trigger.Although this technique has been documented in independent books (e.g., Ken Henderson) the value could be zero from some other action performed by some other trigger. I.e., the value of @@ROWCOUNT does not always reflect the number of rows affected in the table that the trigger is attached to.From BOL:[i]Returns the number of rows affected by the [u]last[/u] statement.[/i]Trust me on this as I have had this experience!Fri, 05 Sep 2008 13:01:45 GMTMauvehttp://www.sqlservercentral.com/Forums/Topic563553-1376-1.aspxI would also guess it depends on what you are doing inside the trigger as well. As I stated earlier, when I did write triggers (not that I don't now when appropriate) I did n't do anything that would have issues (problems) if no records were inserted. Never had any anomilies in my databases that could be the result of a zero record insert.Having learned this, however, is definately an asset for future activities.:cool:Fri, 05 Sep 2008 12:32:24 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic563553-1376-1.aspx[quote][b]Lynn Pettis (9/5/2008)[/b][hr]I must not fully understand INSERT triggers, but I was always under the impression that if nothing was inserted, an insert trigger WON'T fire. I have never had to write an insert trigger to handle a zero row insert; multi-row inserts, yes. I guess this is something I am going to have to investigate.:cool:[/quote]As others have already poitned out, the trigger stil fires even if no rows are affected. That's why many developers make it a habit to start each trigger with this line:[code]IF @@ROWCOUNT = 0 RETURN;[/code]Do make sure that it's the very first line of the trigger, though. I once have spent way too long staring at mysterious results until I realised that I had put this statement [b]after[/b] the SET NOCOUNT ON, so I checked the @@ROWCOUNT from that SET statement (which is always 1). :Whistling:Fri, 05 Sep 2008 12:24:51 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic563553-1376-1.aspx[quote][b]Lynn Pettis (9/5/2008)[/b][hr]I must not fully understand INSERT triggers, but I was always under the impression that if nothing was inserted, an insert trigger WON'T fire. I have never had to write an insert trigger to handle a zero row insert; multi-row inserts, yes. I guess this is something I am going to have to investigate.:cool:[/quote]Took me ages before I realised this was the case (I only found out thanks to some odd problems on a customer db). The same is true of Update/Delete triggers.If a tree falls in a forest & no-one hears it, it may or may not make a sound. But it will cause a trigger to fire :)Fri, 05 Sep 2008 09:04:00 GMTicockshttp://www.sqlservercentral.com/Forums/Topic563553-1376-1.aspx[quote][b]Lynn Pettis (9/5/2008)[/b][hr]I must not fully understand INSERT triggers, but I was always under the impression that if nothing was inserted, an insert trigger WON'T fire. I have never had to write an insert trigger to handle a zero row insert; multi-row inserts, yes. I guess this is something I am going to have to investigate.:cool:[/quote]I hadn't thought about wheter an INSERT trigger would fire if there was no insert, so wrote a quick test.[code]create table ins (num int)create table inslog (ins int,del int, whn datetime)goCREATE TRIGGER dbo.instrigger ON dbo.ins AFTER INSERT,DELETE,UPDATEAS BEGIN insert inslog (ins, del, whn) select (select count(*) from inserted), (select count(*) from deleted), getdate()ENDgo;with n as (select 1 as n union allselect 2 as n union allselect 2 as n union allselect 3 as n union allselect 3 as n union allselect 3 as n union allselect 4 as n union allselect 4 as n union allselect 4 as n union allselect 4 as n)insert ins select n.n from n;update ins set num=6 where num=4;delete ins where num=2;;with n as (select 12 as n)insert ins select n.n from n where n = 4;select * from inslog[/code]The result was:[quote]ins del whn10 0 2008-09-05 15:58:24.2804 4 2008-09-05 15:58:24.2800 2 2008-09-05 15:58:24.2800 0 2008-09-05 15:58:24.280[/quote]So it seems that an insert trigger will fire even if nothing is inserted!Another new item learned from QotD. :)Fri, 05 Sep 2008 09:03:16 GMTDerek Dongrayhttp://www.sqlservercentral.com/Forums/Topic563553-1376-1.aspx[quote][b]icocks (9/5/2008)[/b][hr][b]A further thing to bear in mind - if there's an Insert trigger on the table, then it will be fired in response to option 2, but not option 1. Depending on what it does (& how smart you've been in limiting the processing for a zero row insert) then there may be a further performance hit and/or lock escalation to take into account. Of course you may have good reasons for wanting it to fire for even an unsuccessful insert attempt.[/b]In general I prefer to anti-join the value list back to the insert table since that's much more easily expandable for multi-row inserts & compound keys.My opinion is that there's rarely a truly right or wrong answer to this kind of question - the real skill lies in understanding the strengths & weaknesses of each approach & then choosing the appropriate option for the system it's being applied to.[/quote]I must not fully understand INSERT triggers, but I was always under the impression that if nothing was inserted, an insert trigger WON'T fire. I have never had to write an insert trigger to handle a zero row insert; multi-row inserts, yes. I guess this is something I am going to have to investigate.:cool:Fri, 05 Sep 2008 08:30:57 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic563553-1376-1.aspx[quote]My opinion is that there's rarely a truly right or wrong answer to this kind of question - the real skill lies in understanding the strengths & weaknesses of each approach & then choosing the appropriate option for the system it's being applied to.[/quote]My thoughts exactly.Fri, 05 Sep 2008 06:56:21 GMTAJ-148218http://www.sqlservercentral.com/Forums/Topic563553-1376-1.aspx[quote][b]Sergiy (9/4/2008)[/b][hr][quote][b]Ric Sierra (9/4/2008)[/b]Yes.[/quote]I'd like to see how.I guess I'm not alone here.[quote]Yes.[/quote]Same question - how?[quote]I agree with your POV but both solutions are efective to insert a new record without duplicates (in a simple way, not in a high volume of transactions), and I'm not trying to give a deep explanation just my POV.[/quote]POV without any grounds is worthless.[/quote]Same order...Yes, I'm sure of that, but not today.Yes, you are not alone.Same answer - Not today!Nice sentence.:Whistling:"La vida es muy seria como para tomarla en serio"Fri, 05 Sep 2008 06:23:19 GMTRic Sierrahttp://www.sqlservercentral.com/Forums/Topic563553-1376-1.aspx[quote][b]icocks (9/5/2008)[/b][hr]A further thing to bear in mind - if there's an Insert trigger on the table, then it will be fired in response to option 2, but not option 1. Depending on what it does (& how smart you've been in limiting the processing for a zero row insert) then there may be a further performance hit and/or lock escalation to take into account. Of course you may have good reasons for wanting it to fire for even an unsuccessful insert attempt.In general I prefer to anti-join the value list back to the insert table since that's much more easily expandable for multi-row inserts & compound keys.My opinion is that there's rarely a truly right or wrong answer to this kind of question - the real skill lies in understanding the strengths & weaknesses of each approach & then choosing the appropriate option for the system it's being applied to.[/quote]You right icocks!This comment closes this subject for me. ;)Fri, 05 Sep 2008 05:46:06 GMTRic Sierrahttp://www.sqlservercentral.com/Forums/Topic563553-1376-1.aspxA further thing to bear in mind - if there's an Insert trigger on the table, then it will be fired in response to option 2, but not option 1. Depending on what it does (& how smart you've been in limiting the processing for a zero row insert) then there may be a further performance hit and/or lock escalation to take into account. Of course you may have good reasons for wanting it to fire for even an unsuccessful insert attempt.In general I prefer to anti-join the value list back to the insert table since that's much more easily expandable for multi-row inserts & compound keys.My opinion is that there's rarely a truly right or wrong answer to this kind of question - the real skill lies in understanding the strengths & weaknesses of each approach & then choosing the appropriate option for the system it's being applied to.Fri, 05 Sep 2008 02:28:29 GMTicockshttp://www.sqlservercentral.com/Forums/Topic563553-1376-1.aspxBoth options are identical on cost.Both perform same SELECT and both perform INSERT only if SELECT returns no records.The only important difference s that 2nd option escalates shared locks applied by SELECT to "Insert range" lock if a record needs to be inserted. No existing rows are locked, all other processes trying to access existing records won't be affected.1st options releases all locks applied by SELECT before applying Insert range lock.And this allows another process to get in between and spoil the party.Both options don't use any resources if there is nothing to insert.Fri, 05 Sep 2008 02:13:53 GMTSergiyhttp://www.sqlservercentral.com/Forums/Topic563553-1376-1.aspxdefinitely 1 option has low cost then 2nd, but if we talk about the locks on table then definitely 2nd option is better then 1st one,to minimize the lock on table other option may a use of select hints ie with(nolock) .... in 1st option ....quotes are welcome on itFri, 05 Sep 2008 01:31:22 GMTsqlmastershttp://www.sqlservercentral.com/Forums/Topic563553-1376-1.aspx[quote][b]Ric Sierra (9/4/2008)[/b]Yes.[/quote]I'd like to see how.I guess I'm not alone here.[quote]Yes.[/quote]Same question - how?[quote]I agree with your POV but both solutions are efective to insert a new record without duplicates (in a simple way, not in a high volume of transactions), and I'm not trying to give a deep explanation just my POV.[/quote]POV without any grounds is worthless.Thu, 04 Sep 2008 18:12:12 GMTSergiyhttp://www.sqlservercentral.com/Forums/Topic563553-1376-1.aspx[quote][b]Sergiy (9/4/2008)[/b][hr][quote][b]Ric Sierra (9/4/2008)[/b][hr]I'm not completly agree with the answer, because depends of the context:If you are looking for the best execution plan the answer is #1[/quote]Can you prove it?Open QA, run the query from the answer and see that execution plans are identical.Just 1st option include 2 statements in execution plan, and 2nd one is all in one.Estimation cost is also 50% for both.[quote]If you are looking for less deadlock the answer is #2[/quote]I don't see any difference in regards to deadlocks.Can you prove your point?[quote]But if the target is INSERT a new record, both solutions works.[/quote]You did not get the explanation for the answer.The point of the question was to illustrate that #1 does not always work.Using #1 you embed into your code a chance for unexpected application crashes.[/quote]Yes.Yes.I agree with your POV but both solutions are efective to insert a new record without duplicates (in a simple way, not in a high volume of transactions), and I'm not trying to give a deep explanation just my POV.Thu, 04 Sep 2008 17:26:16 GMTRic Sierrahttp://www.sqlservercentral.com/Forums/Topic563553-1376-1.aspx[quote][b]Ric Sierra (9/4/2008)[/b][hr]I'm not completly agree with the answer, because depends of the context:If you are looking for the best execution plan the answer is #1[/quote]Can you prove it?Open QA, run the query from the answer and see that execution plans are identical.Just 1st option include 2 statements in execution plan, and 2nd one is all in one.Estimation cost is also 50% for both.[quote]If you are looking for less deadlock the answer is #2[/quote]I don't see any difference in regards to deadlocks.Can you prove your point?[quote]But if the target is INSERT a new record, both solutions works.[/quote]You did not get the explanation for the answer.The point of the question was to illustrate that #1 does not always work.Using #1 you embed into your code a chance for unexpected application crashes.Thu, 04 Sep 2008 17:03:40 GMTSergiyhttp://www.sqlservercentral.com/Forums/Topic563553-1376-1.aspxAlso, MERGE isn't available in SQL Server 2005, and our LOB systems will not migrate to SQL Server 2008 until the vendors support SQL Server 2008. We just now (in the last year) were able to migrate to SQL Server 2005. I don't see a move to SQL Server 2008 for at least 2 or 3 years based on past experience.:cool:Thu, 04 Sep 2008 15:37:05 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic563553-1376-1.aspxMERGE is an alternative to reach the target -Insert a new row without duplicates-, but is the last alternative I would choose for this case.You spend a lot of code and avoid the simplicity. Hard to read for.Plus: Using MERGE is a commitment to be MSSQL 2008 dependant. Isn't it?Thu, 04 Sep 2008 15:24:30 GMTRic Sierrahttp://www.sqlservercentral.com/Forums/Topic563553-1376-1.aspx[quote][b]Lynn Pettis (9/4/2008)[/b][hr]Not sure if MERGE is needed here. The question only indicted that a record needed to be inserted if it did not already exist in the database. Nothing in the question indicated that an update should be done if the record already existed. With no update requirement, why use MERGE?:cool:[/quote]Hi Lynn,Needed? No, not needed. The INSERT WHERE NOT EXISTS works great as well.But it's definitely possible to do it with MERGE as well. Though MERGE is a great way to do the "update-or-insert" routine, it has many other possible uses as well. For instance, you can use it to replace the non-standard and dangerous UPDATE FROM syntax (see my blog for details). And you can also use it to do a conditional insert:[code]MERGE INTO CustomerUSING (SELECT @CustomerName) AS New(Name)ON New.Name = Customer.NameWHEN NOT MATCHEDTHEN INSERT (Name) VALUES (New.Name);[/code]I was unable to detect any performance difference with the INSERT WHERE NOT EXISTS version, so I guess it eventually boils down to personal preference.Thu, 04 Sep 2008 15:06:58 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic563553-1376-1.aspxI agree with Christian & Ric Sierra - great question, but the option to choose depends on other circumstances (i.e. indexes, isolation level, how much performance you need.....)Thu, 04 Sep 2008 14:20:05 GMTdmoldovanhttp://www.sqlservercentral.com/Forums/Topic563553-1376-1.aspx[quote][b]Hugo Kornelis (9/4/2008)[/b][hr][quote][b]Christian Buettner (9/4/2008)[/b][hr]I have adjusted my post above to remove the wrong information.[/quote].... that I seem to have overlooked thus far. And now I find myself wondering what it is that you have written.Sad, isn't it? :D[/quote]Well, if you look close enough, you might still find my quote on the page :o)Thu, 04 Sep 2008 10:28:08 GMTChristian Buettner-167247http://www.sqlservercentral.com/Forums/Topic563553-1376-1.aspxNot sure if MERGE is needed here. The question only indicted that a record needed to be inserted if it did not already exist in the database. Nothing in the question indicated that an update should be done if the record already existed. With no update requirement, why use MERGE?:cool:Thu, 04 Sep 2008 10:19:07 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic563553-1376-1.aspxThis was one of the best questions with the best explanation I have seen out there.IT does seem slightly dated now that 2008 is out though. In 2008 it seems that Merge would be the best option.Thu, 04 Sep 2008 10:09:37 GMTtimothyawisemanhttp://www.sqlservercentral.com/Forums/Topic563553-1376-1.aspxCould just be me, but answer 2 just seemed to be the logically correct answer to the question.:cool:Thu, 04 Sep 2008 09:57:02 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic563553-1376-1.aspx[quote][b]Ric Sierra (9/4/2008)[/b][hr]I'm not completly agree with the answer, because depends of the context:If you are looking for the best execution plan the answer is #1If you are looking for less deadlock the answer is #2But if the target is INSERT a new record, both solutions works.[/quote]In my mind, it's the unknown adventure between the locks that should be avoided.Thu, 04 Sep 2008 09:03:38 GMTTom Garthhttp://www.sqlservercentral.com/Forums/Topic563553-1376-1.aspxGreat question! I have seen many co-workers create queries like this that work really well when all of the data can be copied onto memory on their test but when it get to the big production server it brings the system to a halt locking huge numbers of records and if let run long enough causing a huge rollback.Thu, 04 Sep 2008 09:03:18 GMTAlan. T.