A Hazard of Using the SQL Merge Statement

  • Dwain Camps

    SSC Guru

    Points: 86893

    Comments posted to this topic are about the item A Hazard of Using the SQL Merge Statement


    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?

    My advice:
    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]
    [url url=http://www.sqlservercentral.com/articles/St

  • Ken Wymore

    SSCoach

    Points: 16642

    Thanks for the article Dwain. Luckily I only use merge statements for updates and inserts. We rarely do deletes based on our business needs but I will definitely keep this in mind. You probably saved me a headache through this article. Thanks!

  • Phil Parkin

    SSC Guru

    Points: 244746

    That's a nice technique Dwain, thanks for sharing.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • DavidBridgeTechnology.com

    SSChasing Mays

    Points: 649

    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).

    David Bridge
    David Bridge Technology Limited
    www.davidbridgetechnology.com

  • Phil Parkin

    SSC Guru

    Points: 244746

    DavidBridgeTechnology.com (4/3/2013)


    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).

    I agree that MERGE is working just as it was designed and that people should understand that from reading the docs. But the article includes a technique - using a limited-row CTE as the target table to avoid unwanted deletions - which was new to me and which falls outside of your suggested alternative titles.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Dwain Camps

    SSC Guru

    Points: 86893

    DavidBridgeTechnology.com (4/3/2013)


    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?

    My advice:
    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]
    [url url=http://www.sqlservercentral.com/articles/St

  • Dwain Camps

    SSC Guru

    Points: 86893

    KWymore (4/3/2013)


    Thanks for the article Dwain. Luckily I only use merge statements for updates and inserts. We rarely do deletes based on our business needs but I will definitely keep this in mind. You probably saved me a headache through this article. Thanks!

    Thanks for stopping by KW!

    I had been using MERGE in the same way you are and then came along the necessity to block replace groups of records, so I had to use this technique. It does work quite well once you know what you are doing! :w00t:


    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?

    My advice:
    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]
    [url url=http://www.sqlservercentral.com/articles/St

  • shalinder.verma

    SSC-Addicted

    Points: 479

    I agree with David. The title was misleading and I got upset before reading the article and got angry after reading the article, save 'holy moly guacamole'. πŸ˜›

    That said, it does containt some useful information for people who are about to start using MERGE statement.

    Back to the topic - This could be a very specific case for MERGE statement where one is not exactly merging the whole recordset but the subset of a recordset.

  • Phil Parkin

    SSC Guru

    Points: 244746

    shalinder.verma (4/3/2013)


    I agree with David. The title was misleading and I got upset before reading the article and got angry after reading the article, save 'holy moly guacamole'. πŸ˜›

    That said, it does containt some useful information for people who are about to start using MERGE statement.

    Back to the topic - This could be a very specific case for MERGE statement where one is not exactly merging the whole recordset but the subset of a recordset.

    You got upset and angry with an article about MERGE? Take a few deep breaths and relax dude, it's not worth the angst.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • P Jones

    SSChampion

    Points: 12329

    As another MERGE fan I'd use

    WHEN NOT MATCHED BY SOURCE AND (t.ID = s.ID) THEN DELETE;

    in the original merge statement.

    But I've no complaints about the article - one size doesn't fit all so some will like and some won't.

  • Mike McIver

    SSC-Addicted

    Points: 493

    David is absolutely 10% correct.

    The "hazard" is given here:

    "Hastily, we consult MSDN"

    and

    "A quick scan of the article"

    and

    "Obviously this must be the ticket, so let’s give that a try"

    Phil is 100% correct about David's suggested titles. Here are my suggestions:

    "We all know what assume spells"

    "How's this egg look on my face"

    "A solution in search of a problem"

    David looses 90% for the snipe at Microsoft ... long live Bill Gates! David needs to attend the Redmond Reeducation Camp.

  • shalinder.verma

    SSC-Addicted

    Points: 479

    Phil Parkin (4/3/2013)


    shalinder.verma (4/3/2013)


    I agree with David. The title was misleading and I got upset before reading the article and got angry after reading the article, save 'holy moly guacamole'. πŸ˜›

    That said, it does containt some useful information for people who are about to start using MERGE statement.

    Back to the topic - This could be a very specific case for MERGE statement where one is not exactly merging the whole recordset but the subset of a recordset.

    You got upset and angry with an article about MERGE? Take a few deep breaths and relax dude, it's not worth the angst.

    No, please don't get me wrong. I said all with humor!

    chill

  • Phil Parkin

    SSC Guru

    Points: 244746

    No, please don't get me wrong. I said all with humor!

    chill

    Oops! Glad to hear it. Time I visited the coffee machine for some additional humour-spotting beverage :hehe:

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • shalinder.verma

    SSC-Addicted

    Points: 479

    Phil Parkin (4/3/2013)


    No, please don't get me wrong. I said all with humor!

    chill

    Oops! Glad to hear it. Time I visited the coffee machine for some additional humour-spotting beverage :hehe:

    Its time I took humor lessons from Rowan Atkinson.

    On serious note - I really appreciate all the people who take pain and their invaluable time to write these columns/articles. Truly generous work.

  • al_kessler

    Right there with Babe

    Points: 780

    P Jones (4/3/2013)


    As another MERGE fan I'd use

    WHEN NOT MATCHED BY SOURCE AND (t.ID = s.ID) THEN DELETE;

    in the original merge statement.

    But I've no complaints about the article - one size doesn't fit all so some will like and some won't.

    I had the same sort of thought! However, when I tried it I learned:

    "Msg 5334, Level 16, State 2, Line 68

    The identifier 's.ID' cannot be bound. Only target columns are allowed in the 'WHEN NOT MATCHED BY SOURCE' clause of a MERGE statement."

    So, back to the original posted solution.

    This was a fine timely article as I have used MERGE for inserts and updates but was just beginning as trigger that needs to use the delete aspect.

    Thanks for the heads up and saving me a fair bit of grief and frustration. Assuming egg on one's face would negatively affect cholesterol levels, thanks for helping keep mine low?

    Al Kessler
    It's too bad that
    Common Sense Isn't

Viewing 15 posts - 1 through 15 (of 59 total)

You must be logged in to reply to this topic. Login to reply