MERGE vs UPDATE

Question

MERGE vs UPDATE

Lord Slaagh

SSCertifiable

Points: 5727
More actions
June 12, 2018 at 8:23 am

#406678

GM SSC,
I have been on several blogs researching about MERGE vs UPDATE. Which is more effective? I have read some blogs that say "don't believe the hype" about MERGE. It's slow and bulky. I have read other blogs that say MERGE is the best thing since sliced bread. Perhaps the real answer to this MERGE vs UPDATE riddle is simply... "It depends." It depends on the amount of data that you are processing and it also depends on your hardware. But what is the real answer? This does not seem like a straight forward question with a T/F answer.
Your thoughts?
Any help would be greatly appreciated!
Thanks SSC 🙂
Everyone has a plan until they get punched in the mouth. --Mike Tyson

Viewing 6 posts - 1 through 5 (of 5 total)

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

sgmunson SSC Guru Points: 110639 More actions · Answer 1

I've heard quite a bit about MERGE over the years, with much of it in the category of "MERGE is unreliable", and given the occasional difficulty in figuring out exactly what it's doing, I'm only willing to trust it with the very simplest of upserts. There's no way I would trust it with something complex. As far as I can tell, it effectively ends up doing an update and an insert under the covers anyway, so I'm not entirely convinced it's a better solution for anything but the truly simple stuff. Your mileage probably WILL vary, so yeah, it VERY much depends...

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 2

I'm in the same camp as Steve. MERGE had some pretty bad reports in the beginning. It's supposedly much improved but the damage was already done and I've seen nothing remarkable for performance improvements nor true reduction in code so I just continue to do good ol' fashioned "UPSERTS" where I handle the INSERT of new data separately from the UPDATE of existing data. I know a lot of folks will disagree with me but I also prefer to keep them separate so that I don't take a chance on screwing both up if I have to make a change. I prefer "exquisite control and separation" of each, especially when it comes to troubleshooting why something isn't working quite right.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

below86 SSChampion Points: 11515 More actions · Answer 3

I was of the same thought as Steve and Jeff. But recently I've started using the MERGE statement, so far "knock on wood" I haven't ran into any issues.

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 4

For UPDATE vs MERGE, I would choose UPDATE hands down. For an upsert, I would have to test again but I have encountered problems.
https://www.sqlservercentral.com/Forums/1615384/Long-running-MERGE

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Lord Slaagh SSCertifiable Points: 5727 More actions · Answer 5

Thank you all for your responses. I think I am going to avoid the MERGE 🙂

Everyone has a plan until they get punched in the mouth. --Mike Tyson