T-SQL Shortcomings With Merge And Triggers And Stuff (In SQL Server)
Thanks for watching!
Video Summary
In this video, I delve into the nuances of merge statements, triggers, and output in T-SQL, highlighting how they interrelate but often fall short in practical application. I discuss specific issues such as the lack of an action column in triggers when using `MERGE`, which can complicate debugging and maintenance. Additionally, I explore why `MERGE` alone allows referencing source tables in its output while other T-SQL commands do not, questioning the rationale behind these design choices. The video also touches on broader themes about the need for modernization in T-SQL to better serve developers, comparing it favorably with more developer-friendly languages like DuckDB and Postgres.
Full Transcript
Erik Darling here with Darling Data. And, um, forget it. Just, let’s just move on. In today’s video, we’re going to talk about some stuff. And the three things that I want to cover are sort of merge and triggers and output and how they all sort of work almost together, but not quite. And how there are just like various T-SQL improvements and interoperability features that would make working, with merge or triggers or output a whole lot easier on people who develop T-SQL code. Now, uh, I, I, I, I’ve, I’ve, I’ve said it once, I’ve said it a million times. T-SQL is a language that is in dire need of, like, improvement, modernization, uh, just, you know, making it a little bit easier for people to work with. Because as things stand, there are just so many gutches and caveats and just weird edge cases that can crop up. And I realize, you know, it’s computers. Computers are hard. Every programming language has this stuff. There are a lot of things Microsoft could do to get rid of some of the, like, more obvious, like, oh God, why doesn’t that work type thing and get us to the, like, oh, this is a really hard problem. We need to solve it in a very specific way type thing and leave that stuff to, you know, skilled T-SQL practitioners. But anyway, uh, before we go on, if you want to give me four bucks a month, like 25 or so other people give me like money every day, every month to do these videos, there’s a link in the video description for you to do that. If you’re uncomfortable with losing four dollars per month, uh, you can, you can like and comment and subscribe and you can join over 5,000 other data darlings, uh, in their, their happy voyage towards learning more about why they probably shouldn’t use SQL Server.
Because it’s annoying. Uh, if you need help with SQL Server, because it’s annoying and hard, uh, I am great at all these things. Best in the Northern Hemisphere, let’s say. Uh, and as always, my rates are reasonable. If you would like some great training on SQL Server stuff, I have a lot of it at a very reasonable price.
About 150 USD for the rest of your life. No need to, like, resubscribe or anything. Uh, you can get all that stuff with some combination of these things in blue. Or you can also click on the link in the video description and just bypass all the typing. Try to make things easy on you. Um, upcoming events, there are none. Tell me about them.
I’ll, I’ll come up. With that out of the way, let’s talk about this. So, uh, a lot of the, the code below is thanks to, uh, Aaron Bertrand, who has a, who I, I, you know, rather shameless.
Actually, there was absolutely zero shame involved in me copying and pasting code from Aaron Bertrand, aside from some minor reformatting, because his Canadian formatting is strange and bizarre to me. The exchange rate on American, on, on American to Canadian formatting is just like the exchange rate on whatever money Canadian, Canadian uses.
Um, but, so, all that is at this link. Uh, if I remember, I will copy and paste all of this stuff into the show notes. Uh, there’s a great, also a great roundup, um, uh, at Aaron’s site on all this stuff about merge. Uh, and then there’s, uh, a really awesome post by my dear friend and, um, and, um, I would say my concurrency mentor, Michael, Michael J. Swart, uh, on what to avoid if you want to use merge. I think there’s some great stuff in there.
And then there’s an Azure feedback item that addresses one of the things I’m talking about here that, uh, of course is just ignored in the sea of Microsoft feedback items. They’re like ignored by PMs everywhere. So, uh, we’ve got a simple table and I’m going to stick two rows in that table with values one and four.
And then I’m going to create a trigger on the table and the trigger on the table is definitely from Aaron’s post, right? So it’s, uh, insert, update, delete, whatever. Uh, and then it’ll like tell you about some stuff and it’ll print some stuff and it’ll be fun.
Now, this is all okay, right? This is all just fine. Except if you look in that trigger, uh, it’s like if exists select from inserted, uh, if stuff’s in there.
Oh, is there stuff in deleted too? Oh, well, let’s check there. Right?
Like, let’s, we got all this stuff to figure out, right? So this is kind of the first place where I get annoyed with things. Now, if we just run this whole block of code and we’re going to talk through it step by step, these are the results that we get, right? So, uh, when we started the table, we had IDs one and four.
And when we finished with the table, we had IDs one, two, one, two, and three. Why? Well, we had row one in there. We added rows two and three.
We inserted those. And then we deleted row four. Why? Because it didn’t match. Cool. All right. All that stuff seems okay. Now, what annoys me is that when you output stuff from merge, you have this magical dollar sign action column.
And this magical dollar sign action column tells you what happened during the course of the merge. You don’t get this for normal inserts, updates, and deletes, of course, because, you know, it should be fairly obvious what you did from writing insert, update, or delete. But if you have, if you have a trigger that, like, fires for multiple different things, like insert, update, and delete, it would be really handy to have that action column available in the trigger to figure out exactly what you need to do.
Right? Great stuff. It would be wonderful.
It would be fantastic. So, you get that in output, but not in the trigger. The other thing that’s really annoying, or rather, okay, actually, you know what? Screw it.
It’s really annoying. It’s really annoying because it’s only available with merge, and people end up writing one-off merge statements that, like, only insert, only update, or only delete, because merge has one superpower that regular insert, update, and delete queries don’t have.
You can reference another table in the output with merge. So, the typical merge statement, you have the target, right, which is my table, right? And in my table, we have one column called ID, right?
And then, when you merge stuff in, you have all the stuff from the source. And in the source, I added some words in here, like 1, 2, and 3, that match the numbers 1, 2, and 3. And you can, in normal situations, you can’t output those columns anywhere.
But when you use merge, you can reference the source table, right? So, you have the stuff, you have the action column from merge, you have all the stuff that was inserted or deleted, and then we get that word column from the source, and we return that in the output, too.
And that’s why, down in here, we have 1, 2, and 3, in word form, for 1, 2, and 3. And then, I mean, we have nothing for 4, because 4 got deleted. That’s fine, you know.
We can’t just make stuff up. We can if we want, but we might not be right if we just made stuff up. So, T-SQL. Whoever is in charge of you at Microsoft, for the love of God, make it easier to manage triggers.
Make action columns available so people can figure stuff out, so they don’t have to look at inserted or deleted, or inserted and deleted, or inserted or deleted, or some combination thereof. Also, it would be really nice if regular inserts, updates, and deletes could reference stuff from, like, source and target tables.
Just seems to make sense. Why can merge do it and nothing else? I don’t know.
It’s bizarre to me. This is stuff that you need to do, because other database platforms in the world are making versions of SQL, stapling their own chickens onto it, that make life so much easier for developers.
DuckDB is great. Postgres is pretty great, too. There’s a lot of stuff in those languages that makes a lot of sense, that makes, you know, when developers need to do something, they have really easy facilities to access to take care of those things.
T-SQL is missing a lot of that stuff. T-SQL is a very stodgy language in a lot of ways that desperately needs this sort of help. So, if there’s anything you can do, if you’re out there listening, please just start making T-SQL better for people.
You have to appeal to these developers. Right? You are no longer just selling C-levels on things.
Because now, C-levels can look at Postgres and be like, all my developers love Postgres and it’s free. Why am I going to pay seven grand a core or whatever ungodly cloud prices for this stodgy language that all my developers hate? You need to bring those people in.
You need to give those people a big hug. This is more like a strangle. So, let’s pretend that this is just a nice multi-arm hug on a person and not fingers around a neck. Because fingers around a neck is mean.
Unless it’s consensual. Right? So, this is just a nice hug for all the developers in the world. So, you can get them to be like, you know what? That SQL Server is all right.
Maybe we don’t need Postgres. Maybe we’d like a database platform that like, you know, works with all our Active Directory credentials or something. I don’t know. It’s crazy out there.
Anyway, I’m going to go now. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. I hope that someone at Microsoft, our friend Sam, will start fixing T-SQL so that developers can hate it less. And more people will use it.
So, I can keep having clients. You know? I’m going to keep this thing working here, you and me. Anyway, that’s good for me. Goodbye.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.