Now, when I last posted, I’m sure you thought I was done talking about the MERGE statement. You are so wrong, compadre! One more post is absolutely needed!
There are a few issues with the MERGE statement. Well, as of this writing, there are 361 possible issues according to Microsoft Connect – the actual website where Microsoft checks to see what issues exist!
So, if you want to use the MERGE statement, please read through every issue listed on the link above and make sure that none of those scenarios could exist for you. If they don’t, great. Knock yourself out and use it.
But, now comes what seems to be the kicker for most places even attempting to use the MERGE statement – Performance. Compare the performance of a process when using the MERGE statement versus the INSERT/UPDATE/DELETE statement combinations. Now, I’ve seen several blogs and several more real-world examples on my own where it’s just plain faster to use the statements that we all know and love – INSERT, UPDATE, and DELETE – and, as the great philosopher Larry the Cable Guy says “Git ‘R Dun!”
As always, it does depend on the scenarios and environments that the statements are run, but, generally speaking, it is going to be faster to not use the MERGE statement.
For some perspectives on the MERGE statement:
1) http://www.sqlservercentral.com/articles/MERGE/103127/ – Their performance comparison of MERGE vs INSERT/UPDATE.
2) https://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/ – Brings up some of the issues with MERGE and goes on a slightly more in-depth look into performance issues.
Now, obviously, you can use the MERGE statement. I bring it up to close the loop to show how it brings together some of my favorite statements – INSERT, UPDATE, and DELETE. After looking through some of the issues that still exist with it – even after 10 years of being included in SQL Server – I would suggest against using it in any Production environment, but if you would like to try it out in any lesser environment, please go ahead and knock yourself out!
So, next time, be ready for something besides the MERGE statement!
1 comment