MERGE Notes

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.