April 2, 2013 at 10:13 pm
Comments posted to this topic are about the item A Hazard of Using the SQL Merge Statement
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
April 3, 2013 at 12:20 am
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!
April 3, 2013 at 1:27 am
That's a nice technique Dwain, thanks for sharing.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 3, 2013 at 2:23 am
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
April 3, 2013 at 2:44 am
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 3, 2013 at 2:56 am
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 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
April 3, 2013 at 2:59 am
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 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
April 3, 2013 at 3:18 am
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.
April 3, 2013 at 3:29 am
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 3, 2013 at 3:38 am
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.
April 3, 2013 at 3:47 am
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.
April 3, 2013 at 3:57 am
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
April 3, 2013 at 4:09 am
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:
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 3, 2013 at 4:35 am
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.
April 3, 2013 at 5:12 am
P Jones (4/3/2013)
As another MERGE fan I'd useWHEN 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 58 total)
You must be logged in to reply to this topic. Login to reply