Log in
In Defense Of Merge: Non-Deterministic Updates
Video Summary
In this video, I delve into the often maligned `MERGE` statement in SQL Server, defending it against its critics and highlighting its unique benefits. You might be surprised to learn that `MERGE` can actually help catch non-deterministic updates—something a standard `UPDATE` query won’t do. By demonstrating how `MERGE` issues a warning when you attempt a non-deterministic update, I aim to show why this statement deserves more respect in your database toolkit. The video also covers practical solutions for making these updates deterministic using window functions like `ROW_NUMBER()`, ensuring that the data integrity and consistency you expect from SQL Server are maintained. Whether you’re looking to improve query reliability or just want to understand a lesser-used feature better, there’s something here for everyone who deals with complex data operations in SQL Server.
Full Transcript
It may be very little surprise to you. I am Erik Darling with Darling Data. But what may surprise you, may rock you to your very core, throughout your soul, is today I am going to defend merge statement in SQL Server. I know, I know, no one will ever defend it. Right? People will hate on it and other people will say, it’s not that bad. No one ever defend it. I know, no one ever sticks up for merge. I’m going to stick up for merge today. Because, uh, I think there’s a really interesting thing that merge will do, if you write a non-deterministic update, that a normal update will not do. Maybe it should, because shouldn’t everything in a database be deterministic? Doesn’t, does ACID mean nothing to you people? What’s wrong with you? Down in the video description, which is my favorite part of the video, there’s all sorts of helpful links. Like, uh, ones where you can hire me for consulting, buy my training, become a supporting member of the channel for as little as $4 a month. You too can, uh, I don’t even know what, what that would do. But it’s not about what $4 a month does, like, like on its own. It’s what $4 a month does from all of you in the aggregate. Right? That’s, that’s where things get special and interesting. So, gang up on me. Uh, you can also ask me office hours questions, because I like answering questions.
Especially good questions. If you have a good question, that’s even better. I’ll give you a high five on that one. And of course, as usual, please do, uh, like, subscribe, uh, tell, tell your mom about my YouTube channel. She’ll probably dig it. Uh, I’m a, I’m a, I’m a likable fella. Uh, you know, sometimes, uh, um, you should drink when I do these. Anyway, uh, I’ll be at a couple of events coming up in March, which is, you know, a few months. Two months off at this point. I guess like four months off at this point. But hey, um, that gives you, buy a ticket now. So, you know, it’s like verge of selling out probably at this point. So might as well get in there before, like all the butts are in the seats and one of, none of those butts are yours. Right? So, uh, data tune in Nashville, March 6th and 7th. Data Saturday, Chicago, March 13th and 14th. I’ll be doing pre-cons at those advanced T-SQL. You know, the stuff that will blow your mind. Um, it won’t melt your brain. I hate when people say melt your brain. I think that that’s what the kids call cringe, but you are, you can’t, you can learn a lot from a dummy. So you should, should probably do that. Anyway, we are feeling festive today, ain’t we?
Okay. So let’s, let’s go defend merge. So, uh, I’m going to make a table here, a temp table. Temp table is not the problem. The temp table is just fine. Uh, but the problem becomes when we try to update this temp table, right? So we’re just going to put one row in there because all we need to prove this theory is one single row. Right? Uh, and we’re going to use John Skeet cause John Skeet’s just real easy to pick on stack overflow. John Skeet causes, I mean, for his, I’m going to say he causes a lot of query problems, right? Everything John Skeet gets involved, everything blows up. So the thing here, oh, you know what? I’m in the wrong database. We gotta move that over. There we go. Now it makes more sense. Life is, life is grand now. Right? So let’s, uh, let’s insert that one row again. Oh, professional. And, uh, so the, the point here is, uh, if we turn on query plans and we look at the plan for this query, uh, we will see that this query produces a parallel execution plan. Right? And by parallel execution plan, I mean, it, it looks like this, right? Where there’s, there’s parallelism. So great. Right? Uh, everything went parallel and everyone was happy. So that’s, that’s, that’s one of the issues that comes up here. And the, what that issue contributes to is that if we try to update this repeatedly, right?
If we run this update multiple times, SQL Server will update our table with multiple, with different rows on almost every execution. Right? So if we run this pair of queries together, and wait a second for it to run, it does some stuff. Uh, we get post ID. Oh, let’s do our special zoom here. We get post ID 156426. And if we run this again, the amount of time this takes is better work. Uh, there we go. 157291.
And if we run at a third time, I bet that you’re going to be surprised. We get 148352. This is a non-deterministic update. And by non-deterministic, I mean, uh, we don’t know what row is going to end up. As the update values, right? Now, how I’m here to defend merge and why I’m here to defend merge is because merge, unlike the update from center, unlike the update syntax, merge will, will, will, will warn you about these things.
Merge will say, Hey pal, uh, something’s a muck here, right? So we’re going to merge into our temp table, and we are going to use our select query as the source. And we’re going to say when matched, uh, that didn’t go well. We’re going to say when matched and exists, uh, a difference between the sets of columns in here, then we will update the table with, with those, those columns. And if we run this, let me wait a second or another second. There we go. Look, it happened. Oh, oh, merge.
Thank you. Thank you for caring about acid merge, but you could have been a little bit, maybe pithier with this error that we got here. The merge statement attempted to update or delete the same row more than once. This happens when a target row matches more than one source row. A merge statement cannot update, delete the same row of the target table multiple times. Refine the on clause to ensure a target row matches at most one source row or use the group by clause to group the source rows. Oh, merge. Thank you. I mean, you could have just rewritten the whole query for us by the time you spit that book out. But here we are, here we are. And I mean, sure we can, you know, maybe it’s a sign that we could aggregate some stuff or we should have done things differently, but at least it’s nice that merge tells us like, Hey dummy, something’s not gonna, something’s gonna look weird if you run, if you keep doing this, like you you’re screwing up. Right? So the way that we can fix this and we can, we can use merge here for this is what we can do is we can use row number, the row number function to make sure that we limit this to one row. Right? And, and something that is particularly important here is that we do not rely on what is essentially a non-unique column, right? Because we could have, we could have multiple last activity dates, right? Like the same. We, this is not guaranteed to be unique, that we have a unique tiebreaker involved in our window function. So now if we run this merge and I’m going to, I guess, pause here to point out what’s new is this thing here where we are saying we’re only where row number equals one, right? We can only put one possible thing in here. Merge executes successfully, right? We, we still get a parallel plan. All this mergey stuff still happens, but we no longer get that error. And if we run this, we will get back a completely different row than any of the rows we saw before. So when we made this deterministic, not only did we get the same value updated in this temp table, but we actually got what is probably the correct value for our temp table, which is fantastic. And sure, there are other ways you could fix this query. Every time I point out how to fix something, someone says, you could also, you could also, I know there are other ways you could rewrite this to fix it. You could even rewrite the update query with the row number to fix it.
I, I, I, I understand that. The whole point here is that merge will warn you when you write a non-deterministic update or delete, apparently. According to the error message, deletes also have to be deterministic. So if you write a non-deterministic update or delete, merge will warn you. Normal update and delete syntax will not. Granted, it’s probably less of a big deal with deletes because you’re like, well, it’s already gone. Who cares if you try to do it again, right? But that’s what I, that’s my defense of merge today. You can, you can hate it all you want, but at least it does that for you. Okay. Well, thank you for watching. I hope you enjoyed yourselves. I hope you learned something and I will see you tomorrow for something else. All right. Goodbye.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 25% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
The post In Defense Of Merge: Non-Deterministic Updates appeared first on Darling Data.
