I think the title is misleading and a little unfair to the wonderful work Microsoft did with Merge (Despite its being 6 years after Oracle - poor show MS).
This is not a hazard, its a language feature. There was no mistake my Microsoft and correct use of the documentation would save most people. Merge is an advanced command as it rolls several previous commands in to one. Anyone using Merge should read all the docs and fully test that they understand prior to working on any code, even in development.
I think your explanation is good though and the it's always good to see a relatively new language feature being brought to the masses but when I read my email with this title, I questioned whether I might have missed an important feature of the statement so I had to read the article. In reality I simply wasted my time reading about a feature than I use very often in an article that added nothing new (to me).
Had the article been called
Introduction to using the Merge statement
Best practice when using the Merge statement
Common pitfalls when using the Merge statement
then I would not have even read it. Hazard indicates a bug to be avoided (to me).
Hi David! Let me say your point is well taken. I hadn't really intended the title to be eye-catching, but clearly it was to you and probably will be to others.
You are correct that this is a feature and not a bug. It so happened to me that I was guilty of the sin of not reading carefully the effect of that DELETE. I fear that I'm probably not alone. The documentation on MERGE is quite voluminous and when you're looking for something specific, things can get overlooked in all of that detail. That's kinda why I mentioned that "I hastily consulted BOL" and perhaps I should have highlighted that as a danger. So perhaps the article documents my folly. 🙂
Perhaps I should have titled the article "A Hazard of not Carefully Reading BOL." 😉
On the other hand, perhaps a few folks will come along and say "I didn't know this" even if you did. Obviously some folks like Phil may not have seen the workaround, so hopefully there is also some value there.
And thanks Phil for having a read, letting me know it was of use to you and coming to my defense in your last post.
I did say what a great feature the MERGE is in the Intro and I'll reiterate it now. Thanks to Microsoft for including it in the dialect and my apologies to anyone at MS that is offended by my title - I did not mean it to indicate there's a bug in this great feature of T-SQL!
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]