Learn T-SQL With Erik: Stupid OUTPUT Stuff
Video Summary
In this video, I delve into some quirky aspects of T-SQL that might surprise even seasoned database professionals. Specifically, we explore the output clause and its peculiar behaviors when used in certain scenarios. While working on my new T-SQL course, which is part of a series aimed at advanced topics beyond beginner material, I stumbled upon an interesting issue involving the output clause with check constraints. Occasionally, attempting to insert values into a table with a check constraint results in a conflicting message about login time, despite successfully inserting a row. This led me to experiment with using `DELETE` and `OUTPUT` together, expecting a clever workaround for session state management tasks. However, I found that even this approach didn’t work as intended, resulting in primary key violations due to the way SQL Server handles these operations within the query plan. The video is a light-hearted exploration of these oddities, aimed at both entertaining and educating viewers about the nuances of T-SQL.
Full Transcript
Erik Darling here with Darling Data. And this video is, of course, part of my ongoing T-SQL course stuff. This is just, this is a little jump ahead, right? Because this is beyond just like what we’re going to, what I would consider beginner material. But so this is a little jump ahead to some of the more advanced stuff. But it was just one of those funny things that I came across while writing stuff. And me and Bats just got such a kick out of it that we wanted to talk about it now rather than later. So this is a little short video about some weird stuff that you might run into with the output clause. Because it’s amusing to me. So once again, I am working on my new T-SQL course. The T-SQL course is, of course, of course, companion material to the pre-companion material to the pre-companion material. schedule slash the pre-cons, the pre-cons that Kendra Little and I will be delivering and pass data community summit this November. If you are attending that, you can get the material for free, as with your attendance to the pre-cons, of course. Now, if you go to someone else’s pre-cons, screw you. Everybody give you anything free. If you if you’re not going to attend, then you can get it for the presale price of $250. And I’m just waving at it down here in the video description. There is a link where you can you can purchase it there. Anyway, let’s get on with this funny little demo. Now, the first funny thing is this part, right, where just dropping a table if it exists, creating a table with a check constraint, and then trying to insert some values into that table. Every once in a while when you run this, you’ll get this message that the statement conflicted with the check constraint on login time. I guess every once in a while, sysdate time just works, and that check constraint works well, and sometimes it doesn’t, but let’s make sure we get a row in there. Okay, one row affected. Great. We did it, and if we look at that one row in the table, this is what we get. We have spid 121, and this is our login time.
What a beautiful sysdate time that is. Gorgeous. I’ve never seen anything so beautiful in my life. Now, the first sort of funny thing with output is, so like, what I was in my head, I was like, well, let’s say you’re like dealing with sort of a session state table, and you know, you’re like, maybe you have a procedure that does like, you know, like checks to see if a row already exists in there, and if it does, you delete, and if it doesn’t, you insert it. Well, I was thinking like, you know what, I bet there’s a real clever way to use output to do that, and so I was like, I got this.
I’m on this case. Like, I’m on the prowl here, right, and so what I figured I’d do is use delete with output, because when you use delete with output like this, you can nest DML. So notice that we have an insert select here, and the insert, and that, well, the select portion is coming from this, right, so we’re sort of treating delete with output like a subquery, you know, and a lot of the videos, like, one of the things I explain is that, like, in SQL Server, or in databases in general, but we’re using T-SQL and SQL Server, like, like, the result of everything is sort of tabular, right, so like, this is just sort of like a table value and result, right, it’s like a little derived table in there, it’s like a, it’s a table expression with a delete inside it, wild, but we can do that, it’s valid, but it doesn’t work, right, and I’ll show you what doesn’t work, is, you know, we have a delete, right, and in that delete, we are deleting the spid, where the spid equals my spid, so spid 121 should get blown away, and then in the insert, we are selecting, we are reselecting the spid with a new sys date time, right, so we can try to do this, but every time we run this, like, this one, this one never works, right, this one, every time we run this, we get this error message, that it’s a primary key violation, right, we just can’t do it, and it’s, it’s funny to me, too, is, because, like, when you look at the execution plan, you have a clustered index delete over here, right, so that, that happens, right, that’s the very first operator in the plan, clustered index delete, should get rid of that row, be gone, and then over here, we have the clustered index insert, right, so it’s like, hey, check it out, we’re, we’re, we’re going to delete this thing over here, and then we’re going to insert this thing later on, but no, every single time, every single time, primary key violation, and the, the primary key, I mean, granted, you could probably make the primary, you could probably expand the primary key to be both spit and login time, and, you know, not to, not to obviously foreshadow anything, but that, that was just a very surprising thing, but, you know, I thought maybe, maybe I’m being too clever, maybe I’m being just, you know, far, far too clever with things, so I thought maybe if I just did a delete with output into the table, that, that would work, right, because, like, maybe the nesting is just like, it’s too much for SQL servers, SQL servers is like, no, I can’t do, I can’t do it, I can’t figure this out, it’s too many operators in this query plan, it gets all, it’s all, it’s like, just cagey and weird, so I would just do delete, and I would output some stuff into here, but, like, like, I know this would give me a, like, a violation, a primary constraint, key constraint violation, but I just wanted to see if, like, like, it would, it would get past anything, because the, the query plan, you know, it’s, it’s, it’s, it’s, ah, right, so you can’t even get a query plan for this one, and when you try to execute it, you get this, you get a different error message, I’m just trying to find a good place to chop this off, because it’s a little bit longer than the first one, where, now, with, with that output clause, we get something different, you can’t, you can’t even get an estimated plan for it, because SQL Server’s like, screw you, like, violation right off the bat, the target table of the output clause, output into clause cannot have any enabled check constraints, or any enabled rules, found constraint or rule, CK nested blah blah blah, 1DD13137, okay, so it’s just, it’s funny to me, because with the, the one plan thing, you get the, the primary key violation, and it’s like, oh, well, clearly, we can’t, we can’t violate a primary key, but then when you do it a slightly different way, in which you actually would violate the primary key, right, like, there actually would be a primary key constraint violation here, you hit this other one on the check constraint, so I have not, I have not quite found the most clever way of doing this yet, but I’m working on it, so, um, if you’re into that sort of thing, I don’t know, perhaps the $250 that it costs to buy the pre-sale price, buy this course at the pre-sale price, uh, would be worth it to you, if not, you can just wait until it’s fully published and it costs $500, because maybe, maybe it would be so worth it to you that you’re like, no, no, I’m waiting for the price to go up, Eric Darlin deserves his flowers, anyway, uh, thank you for watching, I hope you enjoyed yourselves, I hope you are as annoyed with this as I am, and I will see you in the next video, all right, 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.