There are plenty of reasons not to use a MERGE statement. Many of those reasons have been covered in depth before. Instead of covering them myself, I will point you to another useful post on the subject.
Bugs Microsoft refuses to fix: https://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/
The bugs alone are reasons enough to avoid using it, and also to refactor any existing MERGE statements you have in your code base. Those bugs include ones that have performance implications as well as ones that could lead to incorrect results. Long story short, Microsoft refuses to fix several of them, and some of the other major issues are “by design”!
If you still feel the desire to use them, please keep reading, and I hope by the end of this blog you will be convinced that
- Less statements does not always lead to better code or better performance.
- MERGE is like a VAMPIRE and once it is in your codebase, it’s just a matter of time before it drains your servers of their lifeblood.
Less Statements are Not Always Better!
Most application developers understand that with code what you see isn’t what you get. Sometimes writing a piece of code may be short and concise, but what the compiler does underneath the covers may be quite complex. This is often what separates inexperienced developers from the expert ones. This basic concept can also be applied to the database, by considering what the SQL Server engine does with the TSQL statement. A statement that looks simple may end up becoming quite complex and difficult for the engine to process, and how the processing is done will be influenced by the size of the data and the indexes on those tables, as well as how the query itself is written.
It always makes me cringe when I hear an application or database developer talk about the “ease” of writing a MERGE statement compared to the difficultly of writing separate INSERT/UPDATE/DELETE statements and possibly additional logic for any logging code that they want, wrapped inside a transaction. What I hear is that they buy into the myth that “less code is better” and that they don’t understand they are prioritizing their own ease of writing code OVER how the code performs for the users. <sigh>
Even if that developer can convince me that the only type of MERGE statement in their database is the OVERKILL CASE (cases defined below), I will still push back against it’s use based purely on the fact that having MERGE around at all brings serious risk along with it.
WAYS TO USE THE MERGE STATEMENT
- Uses 1 of 3 underlying statement (OVERKILL CASE)- You only need one type of underlying statement (INSERT/UPDATE/OR DELETE) but instead of just writing that statement, you insist on MERGE most likely because you really love the OUTPUT functionality. For some odd reason Microsoft gave the Merge Output more powers than it gave INSERT/UPDATE/DELETE. Vote for my suggestion here: https://feedback.azure.com/forums/908035-sql-server/suggestions/39249022-tsql-expand-output-clause-functionality. Developers, please stop prioritizing the ease of writing the logging code over performance. Not to mention MERGE is clunky and difficult to write and maintain.
- Uses 2 of 3 underlying statements– At least on the surface I can understand why you bothered with the clunky syntax of MERGE. You may think on some level that you would get separate execution plan operators for each logical part of the MERGE, but you’d be wrong. The engine wants to perform your request all at once, which may mean loading a very big table into memory, locking it up for other users, and taking way too long to do what it really needs to do.
- Uses 3 of 3 underlying statements– Same flaws as #2, but with even more syntax complexity. You’re not gaining anything by writing one seriously complex statement, please stop. Well, you may be gaining the ire of your database administrators who watch these suckers go off the rails.
MERGE is a VAMPIRE!
Let’s assume for a second that you only use the OVERKILL case outlined above and you never see the bugs manifest in your system. I’d personally doubt the veracity of such statements, but let’s go ahead and assume they are true for the sake of argument.
You may have Standards outlined somewhere that explicitly mention MERGE is only allowed for certain scenarios. Unless your processes are TIGHT, someone is bound to come along, see a MERGE statement and take it as permission that MERGE statements in general are OK. They will not see the nuance of it’s use. Guess what? Neither will your pull request reviewers. Some DBA’s may even miss it. BAM! Next thing you know you have a server down in production. Most likely it won’t happen right away because the table it’s written against is relatively small. But just wait. Data has a tendency to grow.
Your code is your standards. Repeat it with me: YOUR CODE IS YOUR STANDARDS.
Merge is like a vampire. Once you invite it in, it’s only a matter of time until it bites you!