>>>
>>>3. Instead of IF EXISTS INSERT I suggest to use MERGE command. It should be much better.
>>>
>>
>>After having worked with it for years, I steer people away from MERGE in OLTP systems. It was really designed for, and is better suited for, ETL loads in data mart/data warehouse scenarios.
>>
>>But your original point is valid - it's best to optimize the IF EXISTS check.
>
>Here is what Alexander Kuznetsov wrote about it
>
>
http://sqlblog.com/blogs/alexander_kuznetsov/archive/2010/01/12/t-sql-tuesday-002-patterns-that-do-not-work-as-expected.aspxFollow up ...look at Paul White's comments towards the end of Alexander's blog entry.
His comments demonstrate that the problem Alexander reproduced isn't so much the pattern itself - it's the way Alexander chose to implement it. I hate to say, "I would never write code that way", but I will definitely say that his sequence of events (selecting based on a MIN...and then starting a try...catch...and then starting a transaction...and then using EXISTS and then a possible subsequent UPDATE....) is probably asking for trouble. Paul's comments are the most compelling part of the entire blog entry.
Context is everything!
