Aaron Bertrand has a really good blog post called "Tip on using caution with MERGE" where he lists all of the bugs and weirdnesses with the MERGE statement. It's a great post and I'm going to bookmark it because every time I use the MERGE statement I try to remember all of the little "issues" with it. This blog post will be a checklist summary of issues that I can use next time I use the MERGE statement. Aaron concludes that it is a productivity booster, but you need to be careful. Good advice.
Here are the isssues:
- Always use HOLDLOCK [MERGE blah WITH (HOLDLOCK) AS targ]. Internally the UPSERT is susceptible to race conditions without it. I thought I wrote about this in the past, but I guess I didn't. I always add HOLDLOCK to my MERGE statements.
- There are tons of connect bugs around MERGE. Most of them, I think, are spurious...I've never seen the problems, but YMMV. Best to be familiar with them.
- Be careful with TRIGGERs. @@ROWCOUNT is not reliable. Aaron has a good repro script. It's probably a minor issue, but the lesson learned is to test your triggers.