>>>>
>>>>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.aspx
>
>Naomi, two things....first, that was written in 2010. In the last few years there have been items on MS Connect regarding MERGE and concurrency.
>
>Second, if you look at Alexander's code example, you'll see there's an extra step involved that makes it a bit different than Frank's example. I agree, Alexander's example is interesting, but it doesn't match Frank's scenario.

He was showing this for UPDATE, but I don't see why the same logic can not be applied to the just INSERT. Other than that and using transactions, I don't see much difference with Frank's example.

So, do you recommend two steps IF NOT EXISTS INSERT instead of

MERGE
WHEN NOT MATCHED
INSERT

?

Do you have specific Connect items supporting this?

Thanks.