SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A Hazard of Using the SQL Merge Statement


A Hazard of Using the SQL Merge Statement

Author
Message
dwain.c
dwain.c
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18023 Visits: 6431
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Ken Wymore
Ken Wymore
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6612 Visits: 2398
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
Phil Parkin
SSC Guru
SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)

Group: General Forum Members
Points: 52216 Visits: 21177
That's a nice technique Dwain, thanks for sharing.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

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.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
DavidBridgeTechnology.com
DavidBridgeTechnology.com
SSC Veteran
SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)SSC Veteran (205 reputation)

Group: General Forum Members
Points: 205 Visits: 234
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
Phil Parkin
SSC Guru
SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)

Group: General Forum Members
Points: 52216 Visits: 21177
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.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

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.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
dwain.c
dwain.c
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18023 Visits: 6431
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
dwain.c
dwain.c
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18023 Visits: 6431
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
shalinder.verma
shalinder.verma
SSC Journeyman
SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)

Group: General Forum Members
Points: 87 Visits: 170
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'. :-P

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
Phil Parkin
SSC Guru
SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)

Group: General Forum Members
Points: 52216 Visits: 21177
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'. :-P

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.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

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.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
P Jones
P Jones
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2876 Visits: 1524
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search