Query Plan Archives | Page 42 of 147

STRING_AGG vs SQL Server’s Optimizer

Posted on December 16, 2024January 22, 2026 by Erik Darling

STRING_AGG vs SQL Server’s Optimizer

Thanks for watching!

Video Summary

In this video, I delve into the intricacies of using `STRING_AGG` in SQL Server and its interactions with the query optimizer. I explore how `STRING_AGG` can lead to unexpected performance issues due to its design, particularly when dealing with large concatenated strings that exceed certain size limits. I demonstrate real-world examples where queries using `STRING_AGG` with a `VARCHAR(MAX)` data type perform significantly worse than those with smaller string types, highlighting the importance of being cautious about the length of the concatenated strings and their impact on query execution plans.

Full Transcript

Erik dishwaskeldarling here, Darling Data. We live to fight another day, don’t we? In today’s video, we’re going to be talking about StringAg versus the optimizer. Now, StringAg was a string aggregation function that was designed to replace all that sort of XML for path type value and VARCAR max stuff that people used to have to put into queries in order to create a list that aggregated strings, usually separated by commas or some other delimiter, or some other delimiter. I guess spaces would be equally valid there. And it’s got some funny optimizer repercussions sometimes, things that we don’t like to see. And it’s actually a very unfortunate side effect of the way StringAg was designed, where rather than Microsoft telling StringAg, hey, if this thing that we’re concatenating together, is big, we should just convert it to a big thing, right? We should just use, like, put a convert in StringAg for the values that we’re concatenating. Because if you’re, like, concatenating integers or something together, like, it’s not like you get, it’s not like you, the problem is, like, you can’t put an integer with a comma. The problem is that if you put a big enough string together, SQL Server’s like, whoa! Can’t do that. You need to convert it.

And then convert that to a string that we, a size that we understand. And so you end up having to write code that looks rather silly. I didn’t mean to exit out of that because, of course, we need to talk about this stuff before we talk about that stuff. You can become a member of my channel for four bucks a month. It’s a pretty good deal. If four bucks a month is, like, all your lunch money, well, like, comment, subscribe. It’s all good stuff in there. I am a SQL Server Consultant. That is how I make my money. Right now, even with the 25 very thoughtful, very generous people who have become members of my YouTube channel, that puts my monthly income from YouTube at roughly 123 pre-tax dollars per month that pays one of my cable bills. So that’s good. But, you know, rent is a much bigger deal. And that’s where the consulting end of my life tends to come in because for less than the cost of one core of Enterprise Edition, we can fix lots of problems.

And then you will have to buy fewer cores of Enterprise Edition or spend fewer monies on Microsoft or Azure’s incredibly overpriced cloud offerings. Screw them. Why give them all your money? Give me your money, then save money. All right. It’s a nice tradeoff. If you would like some very high quality, very low cost SQL Server performance tuning training, you can get all of mine for 150 bucks for life. That beats the pants off all the Black Friday deals you’re going to see. So you should buy that. You can either use that link in this discount code to do it or down in the video description, click on the link in there and it’ll do all that for you. So, upcoming events. Well, you know, I don’t have any dates right now. So if you would like to go on a date with me, you can tell me about your event and I’ll show up. Dress nice. Smell good. At least at the beginning. Don’t know about the end. Anyway, let’s look at this string ag nightmare.

So, this is the thing that happens whenever you have, whenever you write queries with string ag in them. This is where you run into stuff. Is that the, like, when you’re like, so ID is an integer, right? Just a four bytes, whatever. But when we create this thing, SQL Server does not make a big enough data type or something for the concatenated string. And it’s just like, whoa, whoa, whoa. We can’t do that. It throws a dumb error. And then you have to write convert varchar max whatever thing plus the comma thing. Notice that the convert varchar max is not around the comma at all. The comma space is just around that ID column.

So, this is where you can start running into dumb stuff, right? And really, the path of least resistance is, of course, to just say varchar max because who knows when you might have two something point whatever gigs of IDs to put into a string. It’s a really good use of SQL Server, right? That’s good use of licensing money there. Create a two gig string of integers. Great. Thanks. Thanks. We’re good with that. You could, of course, mess with things a little bit. And you can experiment with smaller values, like, say, varchar 500.

And this will, of course, get you around the error and, you know, give you the string that you want. So, the difference between these two things, and this is where you have to be careful, is that when you say max or where you say that the value is above a certain point, query execution times are way different. If you, sorry, not you, I’m not going to make you do any work here.

If we zoom in here, this top query took 17 seconds and this bottom query took 7 seconds. So, the bottom query is a full 10 seconds faster. Granted, there are things about both of these queries that we could fix a bit. But the big problem with the first query is there’s an additional operator in it.

See, there’s a filter here and a filter here. And the filters here are for the halving from the count, right? So, this is going to be a filter no matter what, because SQL Server has to run the query, come up with the counts, and then filter on the count. So, that can only be filtered out after the results are run, after the results are calculated at runtime.

The top query, where the string column is a varchar max, has an additional filter in it that is not down here, right? Like, there’s no thing between the sort and the compute scalar the way there is up here. And this filter is saying where post type ID equals 1. It’s kind of weird.

What’s this filter doing? Greater than 1. What’s this filter doing? Greater than 1. What’s this index scan doing? Absolutely nothing. Just scanning the whole table. There is no predicate in here. What’s this index seek doing? This is seeking to… Oh, gosh darn it.

This is going to be a tough one to frame up. I’ve got to move this over a little bit. There we go. We are seeking to where post type ID equals 1 here. The reason why this happens is because of optimizer costing.

When we do the string ag for a big string, and SQL Server computes a scalar, this is where we compute that big string for string ag. If you don’t believe me, well, that’s too bad. That’s exactly where it happens. For a big string, SQL Server is like, oh, I can’t push a predicate down past that.

For a small string, SQL Server is like, oh, yeah, I can do an index seek for that. No problem. I’m glad you asked. That’s a great idea. The thing is that there’s like a weird level.

Like, I’ve experimented with this number quite a bit. And, like, if I went up to, like, 700 or 800 or even 600, that filter would come back. If I went down a little bit, it would go away.

If I changed compatibility levels, the number at which this changed from having that additional filter with the index scan to having the index seek with no additional filter would change. Sometimes statistic sampling would have something to do with it.

There were all sorts of things that would make this number weird each and every time. So be very careful when you’re using string ag in queries. You might find that using varchar max or even a longer string type than is probably necessary for the convert up there, that you might find that SQL Server all of a sudden starts choosing really stupid execution plans.

You might find that bringing that number down helps quite a bit sometimes. And other times the optimizer is like, ah, changed my mind. Think it costs differently now.

Price has changed, right? It’s like the stocks. They go ups, they go downs. They ruin your chances of retirement. They give you a little bit of hope for retirement.

It never really says, yay, I’m going to retire. Anyway, we do have to be careful with these things. And this is honestly something that Microsoft should fix because there is nothing about this string or what the value of this string is that should prohibit the optimizer from being able to push a predicate down and seek into an index rather than scan an index.

There is nothing going on in there where that should be an optimizer limitation. And, you know, I guess somewhat thankfully I still see most people not use string ag and just use the XML version of it. I’m particularly fond of the XML version of it because, I don’t know, kind of an XML thing.

A little bit. You know, kind of my first SQL Server frenemy was XML and XQuery. So, got a little bit of a crush on that.

Anyway, be careful out there when you’re using string ag. Be prudent with this sort of syntax when, if you are, you know, using non-string values and all of a sudden you need to, you know, concatenate those into a list, whether they’re numbers, dates, bits, I guess. I mean, one thing.

That would be a choice. But, you know, whatever you’re doing in there. Be very prudent with the length of the string that you convert whatever column data to because, you know, you want to avoid the error and you want to avoid truncating things, but you also want to avoid situations where SQL Server no longer decides to push predicates to where they belong and just starts filtering all your data out and making all your queries take way longer than they should.

So, I hope you enjoyed yourselves. I hope you learned something. And I will see you, I think this video is scheduled to go out on a Friday.

So, I do hope and pray that everyone has a great weekend and enjoys themselves to the fullest. All right. We’re good.

Time to turn these lights off. Getting sweaty. Thank you. I love you. 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.

OPTIMIZE FOR UNKNOWN vs. OPTIMIZE FOR VALUES In SQL Server

Posted on December 13, 2024January 22, 2026 by Erik Darling

OPTIMIZE FOR UNKNOWN vs. OPTIMIZE FOR VALUES In SQL Server

Thanks for watching!

Video Summary

In this video, I delve into the pitfalls of using `OPTIMIZE FOR UNKNOWN` in SQL Server stored procedures and why it can often lead to suboptimal query performance. I explore how parameter sniffing issues arise when you optimize for a specific value versus optimizing for unknown values, highlighting that while `OPTIMIZE FOR UNKNOWN` might seem like an easy fix, it frequently results in less-than-ideal execution plans. Instead, I advocate for optimizing stored procedures with specific parameter values to achieve more stable and efficient query performance, even if it’s not perfect across all scenarios.

Full Transcript

Erik Darling here, president and CEO of Darling Data Enterprises. And in today’s video, we’re going to talk about Optimize 4. Why? Well, because I have kind of a funny angle on it. And it’s not that Optimize for Unknown is good. Optimize for Unknown is dumb and it stinks and everyone who I see use it because, uh, perimeter sniffing. I just wish that I had, I wish that there were like a zoom feature for me to send a boxing glove on a spring. out of their computer somewhere and just whack them. Um, it is, um, it is, it is, it is internally deflating to hear these words. Uh, but we’re going to talk about how sometimes, uh, optimizing for a specific value can be a better course of action, uh, than optimizing for unknown. Uh, you do have to kind of know and care and love your data and all that stuff, uh, in order to figure out what you’re doing. What values you should be optimizing for. Because that, that can be a tricky, a tricky enterprise. But once you, once you have found that enlightenment, once you have found that Zen moment, so you have experienced that, that spiritual release, nothing will ever top it.

Promise. Anyway, before we talk about that, let’s talk about four bucks. If you’ve got four bucks a month and you want to be a member of my channel, you can click the become a member link in the video description and do that, do just that. Uh, you can cancel any time as they say. Uh, if, if four bucks a month is more than you can stand apart with, if you, if you care very deeply about every George Washington that comes, comes into your bank account. Uh, liking, uh, liking, commenting, subscribing are, are just wonderful ways of becoming part of the, the darling data community of data darlings. Uh, you can join over 5,000 other people who have, who have joined the ranks of the, the darling data, data darling army. Uh, if you need SQL Server consulting, I am world class at all of these things. Um, we don’t even need beer gut magazine to tell us that anymore. We just, we just know from experience. Uh, and as always, my rates are reasonable.

If you would like some very high quality, very low cost training. And if you’ve looked around at the black Friday offers, uh, that other people have out there and you’re like, wow, that’s still hundreds or thousands of dollars. Uh, and that’s only good for a year. Uh, you can get all mine for 150 bucks for the rest of your life. Uh, or just about 150 bucks USD. Of course, we don’t, I don’t accept other currencies. Someone else has to do that conversion and translation for me. Uh, you can, you can either click on the link up there and use the discount code spring cleaning or click the link in the video description and you can get both.

You can get all of that. Uh, upcoming events. I guts none. Oh no, I don’t, I don’t have to go anywhere. Uh, shame. I’ll just stay in my underwear at home. But of course, if you would like me to show up to your event, either in my underwear or maybe in some sort of, you know, vaguely business casual wardrobe, it might look a lot like this. At least from, at least from the top up, uh, then, then, then let me know what your event is and maybe, maybe we can, maybe we can figure something out.

With all that out of the way, let’s, let’s get into this, this fun, fun thing that we have to talk about. Now, uh, I’m going to start this store procedure off written in a way that I personally don’t like. All right. Cause this will lead to all sorts of problems. This is one of those things.

When I say, when I, when I say the words, anything that makes your job easier makes the optimizer’s job harder. This is probably like at the top of the list of those things. Cause this is a pattern that I see in almost every single consulting engagement somewhere in a query that someone is having performance problems with. This is never a good sign. Uh, and it’s not, it’s not good for a number of reasons. Um, you know, uh, we’re going to, I’m going to say parameter sniffing is one of them, but, uh, you know, it’s, it just ends up with some really ugly consequences. Now I’ve already run all the queries for this.

And if you are, and if you’re able to look under my armpit there, you might see the number eight minutes and 31 seconds, right? Eight minutes, 31 seconds. And, uh, the, the general gist of this is that as long as you are searching for an owner user ID, as long as this is not null. And you might have a, you might have a store procedure with, you know, a pattern somewhat like this, where you are just guaranteed to always get an owner, like a, see the equivalent of that ID passed in, you might do okay. Right. It might, you might just not have ever have like a terribly big problem with this pattern.

But as soon as people start doing searches for other stuff that maybe don’t focus on a specific owner user ID, that’s when you run into issues. So the first three executions of this are all looking for an owner user ID. This is all populated in here, right? This one, this one, this one, uh, where we do, oh, that was a, that was a bad, that was bad zoom and etiquette on my part there. That did not go well. Uh, but for these bottom two, um, you’ll see that, uh, we do not have owner user ID populated in there.

And that’s where this query starts to run into trouble. Like this one does okay. Right. Up at the top where we search for 22656. And I want to point out something out here that is kind of fun, uh, is that these queries actually do all get the parameter sensitive plan optimization stuff. That’s why the ones, that’s why the ones that you see are slightly different, um, in, uh, in the first few queries, right?

So like this plan, uh, because parallel has certain amount of, it has some estimates associated with it. This plan is not parallel. It gets an estimate of 117. This plan is not parallel. It gets an estimate of 95. And like the, the, the serial plans are slower, right? That’s 1.7 seconds to scan that that’s one, again, 1.7 seconds to scan that the parallel plan is the fastest of the bunch.

But even if you look at the parallel plan, like we have an index up here that leads on owner user ID, right? That one, this fantastic index right here, this single key column index that I would probably also make fun of if I saw in real life. But we have an index scan here. We should be able to do an index seek, but we can’t because we’re doing the, you know, column equals parameter or parameter is null.

You can replace this with any variation on the, on the thing where it’s just like column equals is null parameter column, whatever. You’re still going to see this same sort of thing here where you, you can’t seek into the index book. Then like these two all have, these two both have the same thing, but there’s an, there’s an additional thing in all of these in the key lookup where we’re evaluating additional predicates.

This is part of what makes this demo sort of sparkle, but like this, like this is also not a good sign to see in your query plans, right? You don’t want to see this stuff over and over again. Now down here, this is where these queries really start to have problems because even with the parameter sensitive plan optimization kicking in, right?

If you look down here, these are the last two things that executed. Uh, we’re, we’re getting like weird, like whatever query variant we’re getting for this is not so hot, right? That’s kind of bad in there.

Uh, so we get the, like the query variant plan for, you know, 95 rows, which is this one. Um, and then we get the, we get that same one again down here, but these execution plans just don’t work well at all for, uh, the, the queries that we get. That’s seven minutes and 44 seconds.

All like all that time, like aside from like, you know, the 51 seconds that you see up to the nested loops join that all that time is in that sort spilling. And down here, we don’t exactly have the sort spill, but we do have like about the 40 seconds of time spent, uh, just like between the lookup and this and everything else. So this, this pattern obviously doesn’t work terribly well.

Okay. So avoid this one as much as you can. Uh, what I see a lot of people do is even is when they do this or when they have other store procedures where things sometimes act up is stick this optimized for unknown hint in there. Now this does turn out better than the query pattern I just showed you.

I admit that it does turn out better, but it’s still not great. Still don’t love it. Uh, and the plans change for these, right?

Uh, where now rather than do any sort of nonclustered index thing, we just scan the clustered index every time. Uh, granted, we don’t have anything that spills for seven minutes, but this is not exactly the plan shape that you would want to see, right?

This is not, this is not exactly fun. And this whole predicate in here is still a problem, right? So that query problem is still an issue with the optimized for unknown. We just take away the cardinality estimates that might’ve happened.

And we replace them with the, the optimized for unknown sort of thing. And then we just get these sort of crappy plans. The two down here still have problems, right?

This one takes a minute and 11 seconds with a lot of that time spent still in the sort, right? Not a fun time to spend, not a fun amount of time to spend spilling. And this one takes 1.2 seconds.

Again, just scanning the clustered index. So the optimized for unknown hint gears all these queries towards a clustered index scan away from a lookup plan, which helps a little bit sometimes, but it’s just sort of not what we want to see overall.

Over here, I’ve got this store procedure set up to optimize for a specific set of parameters that work out pretty well across the board, right? So this is like, rather than say optimize for unknown or let SQL Server do a thing every single time, we’re going to, or even cache and reuse a plan or use parameter sensitive plan optimization stuff.

We’re going to tell SQL Server, every time this runs, I want a plan for when these parameters being these values. And this works out a bit better.

Not perfectly. We still have a scan of, we still have the same problem with the query pattern itself, right? So like, like ideally fix the query pattern. But if you’re kind of hamstrung and that’s too hard for you or whatever, you might be better off just saying, hey, these values work really well to get me the plan that I want.

So we still have the problems in here. We still have the predicate stuff in here that we don’t want, but we get like at least a sort of stable plan across.

And even for the two second query, the two final queries in this, these end up better than the first time around. The last one down here is a bit slower than the optimized for unknown version.

But when you take into account that this one no longer takes like, you know, eight minutes or like, like almost two minutes or whatever that was, like the time you save on most of the executions for this is a lot more helpful.

Granted, this one, you know, we, you know, we’re going to be ivory tower about stuff. We should really fix that query pattern instead. But if you’re going to do something like this, like the time that you lose on this one is made up for by the time that you gain on this one.

So still not great down here and still not great here, but a lot better than we saw with the original query pattern. And from with the exception of this one, with the optimized for unknown pattern.

So whenever you’re, you know, sort of digging through store procedures that have this problem, and if you’re using optimized for unknown in places, then you should probably consider, you know, figuring out a good set of sort of, let’s just call them store procedure defaults and optimizing for those instead, because you can generally find a really good execution plan for a set of values that’ll work pretty well across a lot of other sets of values.

Might not be perfect. Might not, but there might be regressions in some places, but it’s better than like almost the full thing being a regression. Like you’re like, I often see with optimized for unknown.

So anyway, hope you enjoyed yourselves. I hope you learned something. I hope you will, I don’t know, maybe optimize for specific values rather than optimize for unknown. And I will see you in another video shortly.

Maybe we’ll see. It depends on how cute I’m feeling. Anyway, goodbye.

Going Further

T-SQL Shortcomings With Merge And Triggers And Stuff (In SQL Server)

Posted on December 12, 2024January 22, 2026 by Erik Darling

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

How To Return A Dummy Row Instead Of Empty Results In SQL Server

Posted on December 11, 2024January 22, 2026 by Erik Darling

How To Return A Dummy Row Instead Of Empty Results In SQL Server

Thanks for watching!

Video Summary

In this video, I discuss a common scenario where you might want to return a row even when your query returns no results. This is particularly useful in analysis scripts or debugging sessions where it’s important to know that the search criteria didn’t match any records. To demonstrate, I show two different methods: one involving a clever use of `sys.databases` and a union operation, and another using an outer join with a derived table. Both approaches are effective and have minimal performance impact, making them valuable tools in your SQL scripting arsenal. Whether you’re a seasoned DBA or just starting out, understanding these techniques can help improve the clarity and usefulness of your queries.

Full Transcript

Erik Darling here with Darling Data. I’m cool. In today’s video, we’re going to talk about how you can return a row when you actually find empty results. This is something that I use a lot in my analysis scripts because sometimes you run a query to try to find something and it doesn’t matter if you’re debugging or if you’re returning results. Sometimes people want to know that they didn’t find a row. rows for something, right? It’s a good thing to know that like, oh yeah, we checked that but we didn’t find anything. It’s a reasonable thing for some people to want to do. So, we’re going to, I’m going to show you two different ways to do that today. If you like my channel, you can join the like 25 other people who have memberships and give me like four bucks a month, sometimes more. There are some very generous people out there in the world and I thank you kindly for your generosity. If you’d like to join their ranks and also be thanked kindly, there are some very generous people out there in the world. And I thank you kindly for your generosity. If you’d like to join their ranks and also be thanked kindly, there’s a video, there’s a link in the video description that says like become a member and you can become a member. If you cannot part with four dollars a month for whatever reason that you are keeping secret from me, we shouldn’t keep secrets, you know, because we’re in love. But if you just can’t, you know, if you’re just being like, there’s like some financial infidelity between us, you can like, you can comment and you can join the over 5,000 other data darlings out there in the world who have subscribed to the channel and who get, hit on the head with a very small hammer every time I publish a video. If you need help with SQL Server, I am the finest SQL Server consultant known on Earth. There might be better ones elsewhere in the world or other, maybe like in the multiverse or something. On Earth, I just haven’t found one yet. So you can hire me to do this stuff or anything else. And as always, my rates are reasonable.

Why didn’t that? There we go. I’m the least good power pointer on the planet. That’s why all my presentations are just demos. If you would like some very high quality, very low cost training, you can get all of mine for $150 about USD for the rest of your life by either clicking on the link in the video description or going through multiple steps out of your way to go to that URL and enter that discount code. And it can all be yours. You can have me for 24 hours. It’s a bit of an indecent proposal, but I promise I’m wearing almost the exact same Adidas attire in those videos. I have no upcoming events. I’d love to have an upcoming event. Tell me about your events. I’ll get there eventually.

All right. Let’s talk about returning rows when you don’t have one. So I guess contextually, we should go into Stack Overflow. It doesn’t matter much for this demo. But let’s say that, you know, like normally you could do something like this, which is multiple steps and kind of annoying, right? So you create a table. Well, in this case, I’m using a table variable because it doesn’t matter. Performance is not an issue here. And then, you know, we insert data into that table variable.

And if the row count from that table is greater than zero, then we select data from the table. And if it’s, if it’s zero or I guess lower, right? Then we return this thing that says the message, like the return, this message that says the table is empty. Well, you know, that’s okay, but it’s like multiple steps and you got to begin and end and mind all, mind your P’s and Q’s, buster, and all sorts of other stuff that just kind of not fun to do.

But there are other ways to do that like this. And this is a, I’m particularly fond of this one because I think, I think this one is quite clever. Where if the, we select from the thing, right? In this case, we’re selecting from sys.databases and we don’t, there’s no database ID higher than this. So we can never find anything. And then we union all that to this. But on the union all part of the query, we have this, say, where not exists select from this CTE up here.

So if nothing ends up up here, then we return results down here, which is wonderful, right? Because if we don’t find a result all in one query, we can just spit a, spit a row back out, right? Say there was nothing in there, right? So that’s, that’s my favorite way of doing it. And of course that the inverse works where if we do find rows, right? We’re just going to say where database ID is greater than one. We get all the rows back that we would care about, but not the dummy row from the bottom one, because something did exist in the CTE up there.

So that’s one way of doing it. Another way of doing it. And I have the written version of this on my blog, on my blog, in a blog post on my blog, the blog, the blog. And someone in the comments left this as a suggestion. And I think this is also clever, where you’re, you have like a derived thing, which is like kind of almost the same thing. And then you left outer join that to whatever, you know, query you care about. And if you don’t find anything in that query through the magic of is null, you can return a result that just shows you when the thing was empty.

And of course, if you run that for a query where something does exist, you get all the rows back. So there’s two, two different ways that I’ve found. Well, one that I’ve found and one that was given to me on my blog by, I forget who it’s been a while, but they gave me that, that, that suggestion. And that works pretty well, too. So if you are ever writing something and you’re like, well, I don’t want to just return an empty result set.

I want to return like a dummy row that tells people, hey, we didn’t find anything. Those are a couple of ways to do it. And they work pretty well. And there’s not really any performance impact to them. So good stuff there. All right. Cool. Well, that’s it for me in this video. Thank you for watching. I hope you enjoyed yourselves.

I hope you learned something. And remember, if you are a major network executive and you’re looking for a young, handsome talk show host to interview Hollywood celebrities about their lives, interests, their loves, passions, I’m available. Sooner rather than later. I won’t always be this young and good looking. Someday I’ll be old and good looking. Anyway. All right. I’ll have to, I’ll have to submit some headshots then. All right. Goodbye.

Going Further

When Function Rewrites Need Query Rewrites In SQL Server

Posted on December 10, 2024January 22, 2026 by Erik Darling

When Function Rewrites Need Query Rewrites In SQL Server

Thanks for watching!

Video Summary

In this video, I delve into a tricky issue involving query rewrites for inline functions in SQL Server. Specifically, we explore the challenges that arise when rewriting scalar UDFs to inline table-valued functions and integrating them back into existing queries. The process can be quite frustrating due to obscure errors like “Aggregates on the right side of an apply cannot reference columns on the left side,” which require unconventional query rewrites using CTEs or derived tables to resolve. Despite these quirks, understanding how to navigate such issues is crucial for optimizing performance and maintaining clean, efficient SQL code.

Full Transcript

Erik Darling here with Darling Data. In today’s video, we’re going to talk about query rewrites for inline functions. And I know this is kind of a hard one to title because it doesn’t have a very apparent title. We’ve spent a few videos talking about rewriting scalar UDFs to make them inline functions and some of the weird stuff you have to do to get that working sometimes. But this one, there’s a very, the function rewrites for the function rewrites for the function rewrites for the function rewrites for the function rewrites. What happens though is when you start using the inline version of the function in the query the way it was written before, you start getting weird errors. And we need to avoid those errors because no one likes errors. You would think this sort of thing would be easy, but it’s a database. So nothing is easy. That’s why I have the problems that I do in life. Anyway, if you like this channel, you can join the nearly 25 other people who support this channel by signing up for a membership. If assuming you’re okay with parting with like four bucks a month, if you’re not okay with parting with like four bucks a month, you can like and comment on the videos and you can even join the over 5000 other data darlings out there in the known universe and subscribe to the channel. So you get notifications whenever I publish a video. It’s a great trade. It’s an awesome trade off. You get a ding and a video and I get to sweat under hot lights. Everything’s coming up you. If you need help with your SQL Server, I am the best SQL Server consultant in the world. A lot of my clients have worked with other SQL Server consultants, thrown their hands up after not getting results and come to me and gotten results. It’s wonderful being able to do that. It’s also very fun seeing the login names from other consulting companies in the world still on those SQL servers.

Hi out there. If you would like some very high quality, very low cost SQL Server training, you can get all mine for about 150 US dollars a month, not a month for life. It’s not a month. I don’t know why I said that. I think what I was going to do is tell you I don’t have a subscription where I charge you a month by month or year. It’s for life. You can get it all. You can either go there and use that code or you can just click on the link in the video description if you’re feeling particularly lazy. And you’ll end up at the site. With the coupon code applied. It’s magical. Since this will be airing after Past Data Summit, I have no upcoming events. If you have an upcoming event, let me know about it. Maybe I’ll come to your up event. I don’t know. We’ll find out. Anyway, with that out of the way, let’s finally do this. Let’s say that we have a scalar UDF. It looks something like this. It’s really not a big deal. Everything’s fine there. Everything is scalar UDF-y. Everything in there works.

And when we run the query, we get a particularly UDF-y plan. It’s a query that has all of the hallmarks of scalar UDF problems in it. This will run for a couple more seconds. And if we look at the execution plan, we’ll see that we have a compute scalar that sucks up the majority of the execution time in this query, right? Almost 11 and a half seconds of time. Well, I guess minus a little bit from over here. So I guess about 10 and a half seconds of time.

And then if we get the properties of this, we will have this fun warning over here. We will have this. I’m sorry that the properties window showed up like that. That is unintentional. We will have this non-parallel plan reason yelling about our scalar UDF. Now, I know what you’re thinking out there. Ah, SQL Server 2019, UDF inlining. I know. The thing is, not a lot of people are in compat level 150.

And even if you’re in compat level 150, there are still a lot of scalar UDFs in the world that cannot be inlined automatically by that feature. So there’s still a lot of UDFs out there to rewrite. Trust me, I know. I rewrite a lot of them. That’s another reason why I have many of the problems that I do.

So this is a fairly straightforward function to rewrite to be an inline table value function, right? This is just fine. You just do the same thing with returning a table rather than returning the value like you do up here, right? So whatever. Not a big deal. The problem is, if you try to stick that in the select list the way that you do, or the way that we did with the scalar UDF, we get this error. And it’s a very obtuse error.

Aggregates on the right side of an apply cannot reference columns on the left side. Well, I don’t see any applies in here. We can’t even get an estimated plan to see if there’s an applied pattern somewhere in there. It’s just, you know, where we have a subquery and that’s, I don’t know.

Like, you couldn’t write it as cross apply because then it would definitely be an apply. Or even order apply would still be an apply. Apply is the key word there. So there are two ways that you can fix this, rewriting the query.

And they’re both stupid. You shouldn’t have to do this. This is asinine. Asinine to the very core, to the back teeth, as some might say.

Okay. So you can either use a CTE and you can do the aggregates here and then pass the aggregates to the function here. And guess what? This will work just fine. We get a much faster parallel execution plan.

So this is okay. And if you’re like me and you just like to use derived tables because you don’t want to use CTE because you don’t want anyone to get the impression that CTE are really any better than derived tables, you can do that too.

And you can get the same fast parallel execution plan. Ain’t life grand. So if you’re rewriting a scalar UDFs and you run into that error where SQL Server is like, oh, no, apply, right side, aggregates, blah.

All you have to do is rewrite your query a little bit to do the aggregates from something in something and then select from that something. And those aggregates magically work in your inline table valued function.

Who would have thunk it? Crazy out there. Anyway, I’m going to just go cry now. I wish I was good at something other than databases.

Maybe I’ll get discovered on here for something else. I don’t know. If anyone needs a young, handsome talk show host, I’d be happy to talk to Hollywood celebrities for almost any amount of money because they’d probably end up liking me a lot and bringing me into their inner circle, most trustworthy friends, and go to cool parties in the hills.

They could buy to all these pains, these sufferings. Anyway, thank you for watching.

Going Further

Choosing Between Triggers And Foreign Keys In SQL Server

Posted on December 9, 2024January 22, 2026 by Erik Darling

Choosing Between Triggers And Foreign Keys In SQL Server

Thanks for watching!

Video Summary

In this video, I delve into when you might want to consider using triggers over foreign keys in SQL Server. Erik Darling from Darling Data shares insights on how cascading actions can lead to unexpected issues, especially with the new cardinality estimator. I highlight a scenario where foreign keys cause performance problems during foreign key validation and discuss potential solutions like query hints. The video also covers best practices for writing efficient triggers, including setting transaction isolation levels and using hint options to ensure optimal performance. If you’re interested in more SQL Server tips or want to support my channel, consider becoming a member by clicking the link in the description.

Full Transcript

Erik Darling here with Darling Data. And in today’s video, we’re going to talk about when you might want to consider using triggers over foreign keys in SQL Server. There are times when you may want to do this because leaving foreign keys up to their own devices can cause all sorts of weird stuff, especially when you have cascading actions involved, especially when you might care about SQL Server’s query plan choice when when cascading those actions out or even when validating foreign keys on insert, update, and delete. Of course, foreign keys and triggers are great ways to maintain referential integrity in OLTP databases. If you have foreign keys or triggers in your data warehouse, you should be dragged out into an alley and beaten with a rigid implement that I will not apologize for the length of the way that I have been doing. I have been apologizing for the length of some of my videos. If you would like to join the 20 or so other people who have been so kind as to become members, and I will not apologize for the length of my members list of this channel, you can do that by clicking the link in the video description. If for some reason you think you have something better to spend $4 a month on, you can like, you can comment, you can subscribe. You can do all sorts of nice things that make me feel less lonely when I wake up at five in the morning and look at my phone. If you need help with SQL Server, I am great at all of these things. And you know what else is great? My reasonable rates. Bam, sold. Pretty good there. Gotcha. Gotcha. You’re gonna be knocking on my door any second now. If you would like low cost, high quality SQL Server performance tuning training, that’s far cheaper than anything you will find on Black Friday. For the rest of your life, not just for a year, you can get over 24 hours of performance tuning training from me at that link with that discount code. There is also a fully formed link in the video description that you can click on without having to do any work or copying and pasting. If you would like to catch me live and in person, Seattle, you can go to Seattle. That increases your chances of your chances of seeing me by a bit. If you actually attend past data summit, you increase those chances exponentially of seeing me. And if you come to me and Kendra’s pre cons on November 4th and 5th, you are nearing a 100% certainty that you will see me. Right? Like, can’t rule anything out. Maybe I’ll like get struck by some kind of weird plasma bolt and turn invisible between now and then. But you will at least still hear my booming voice and see a floating lavalier mic going around the stage. If I turn invisible for past data summit. Well, I mean, just watch out on kilt day. We’ll have some hijinks going on. Anyway, let’s go talk about triggers versus far and keys. And the post that, of course, inspired this, because unlike some other SQL Server websites out there, I like to give credit where credit is due.

And my friend, my good friend, Forrest McDaniel, who I got to catch up with at Data Saturday Dallas, wrote this post in 2018. God, I was still in my 30s. Is this really six years old? Yeah, yeah. In like a month, this thing is like exactly six years old. Anyway, here’s a forest demo in a canute shell. You have a table called P that’s sort of like parent. You have a table called C that’s sort of like child. And you put some data in the parents and you put some data in the children’s and you add a constraint to that table. And let’s just make sure this thing is actually on there. So nothing weird happens. This is the problem that Forrest ran into. And this is a problem that I see a lot more people running into as they start flipping to higher compatibility levels and start succumbing to like Oregon Trail style succumbing to the new cardinality estimator.

So what I’m going to do is force the default with endless air quotes cardinality estimator. That’s the new one. And what you’ll see is a plan that looks like this. And this is not the kind of plan that you want to see when you are validating your foreign keys. This is a very bad plan for foreign key validation. This type of plan will generally be a lot slower than the nested loops variety that you would normally want to see here.

And this type of plan in a foreign key greatly, like you going to Seattle and going to my pre-con, how that exponentially increases your chances of seeing me. Seeing this type of plan greatly exponentially increases your odds of seeing a whole lot of deadlocks, especially if two things try to delete from these tables at the same time. One really sort of interesting, I’m not even going to call it downside, just one very interesting effect of cascading foreign keys is that under the covers they will switch to the serializable isolation level.

And you can, you know, like that’s a pretty strict one. If you come to my session at Past Data Summit about isolation levels, you will learn more about that. Really tying things in today, this is a big sales pitch for Darling Data.

This is the kind of thing you really want to avoid. Now, there are ways to fix this. But if you are like, you know, in any framework or some other ORM only shop, you might have a hard time injecting query hints into your queries.

You know, you might not be able to force a plan because of, you know, differences and stuff. It might not, just might not go well. You can fix this problem with a cardinality estimation hint like this.

Where SQL Server now, because you use the legacy cardinality estimator, SQL Server estimates joins differently, right? There’s a difference in how SQL Server looks at join cardinality between the two. That’s one of the biggest differences between them.

And now we get a nested loops join and things will be much happier as far as when you actually have to cascade deletes out. Because, you know, when you cascade a delete, you’re not just deleting from one table anymore. You are deleting from two tables.

You are deleting from up here. And then you spool a bunch of data into this thing. And then you spool a bunch of data out of this thing. And then you join that spooled data to the other table where the cascading thing lives. And then you delete from another thing.

So all in all, you are deleting from two tables. And the more indexes might be involved here, like say you have a whole bunch of indexes on both of those tables for all the different queries that you’re on, the more stuff you’ve got to delete from, the more stuff you’ve got to lock, the more problems are on.

Goes without saying. All of that stuff. So another way of fixing that is to stick a loop join hint on here. Now, what you’re going to notice if we look at these two plans together is that these are nice, thin, friendly looking lines.

And these are not so thin, friendly looking lines. The reason why is not because of anything other than the cardinality estimator still. You would still want to see this plan even in this state.

The one up there, you get a nested loops join because SQL Server does a better job with cardinality estimation in this case for that join. The bottom one, we’re forcing a loop join, but SQL Server still uses the same cardinality estimation for the default cardinality estimator. That’s why those plans look different.

Both of these use apply nested loops. There’s no prefetching in one and not in the other. There’s no optimized nested loops in one and not in the other. Everything is the same except the cardinality estimation model for that. So if we were to add option loop join and force the legacy cardinality estimator, we would see just basically a plan that looked like the force the legacy cardinality estimator one.

That one only looks different there because it’s only the loop join hint, not the cardinality estimation hint. Now, if you wanted to write triggers to replace this stuff, you would have some stuff to think about. Right.

Because, well, I know thinking isn’t your specialty. I’ve seen your queries, seen your servers, seen your schema, seen your indexes, seen a lot. I’ve seen a lot.

I know I’m like Santa Claus when it comes to SQL Server. I see everything. I see everything. And so there’s some stuff that you have to do inside of your triggers to make them work right or to make them work well or work better. A lot of that stuff comes at the very top.

For example, it’s very good to have this condition at the very beginning of your trigger so you can just bonk out if there are no rows. Right. So if someone does an insert that doesn’t actually insert anything, you would want to do this to avoid having to run anything else in the trigger.

You generally, even though exact abort is the default for triggers, I find it’s a lot safer to set no count and exact abort on here just in case any client options or client settings changes may have tinkered with this. And then you want to set row count to zero, I guess because Paul White says so. So we’re going to listen to Paul on that one.

Now, since behind the scenes, cascading foreign keys use the serializable isolation level. If you are in an environment where that sort of thing might matter, you would probably want to set the transaction isolation level inside of your trigger to serializable as well. So that you get commensurate blocking results when these things execute.

Like I said before, that can tip the scales towards things having a lot more locky and deadlocky. But, you know, this is the sort of this is the price you have to pay if you need this level of consistency in your queries. Another thing that you might have to consider is and this and this comes down to sort of the control thing that I was talking about before, where, you know, if you know, you always want to loop join for this stuff, you can stick the option loop join hint in the trigger.

And you can always get that option loop join. You can always get that loop join plan that you’re after. You can also always put a force seek hint into this part of the query so that you never have to worry about a scan happening on the inner side of anything.

Right. Not on a merge join, hash join, but because we’re only getting loops here. As long as you have a good supporting index, that loop join will always you want to force the seek in there.

Where you have to think about stuff a little bit, though, is if you are using an optimistic isolation level, you might need to add this in because otherwise you might get strange results from reading stale data. Now, Paul White, and I’ll put a link to this post in the video description as well. Paul White goes over this in one of his isolation level series blog posts, sort of explaining something similar.

But, you know, so if you’re using RCSI, you would probably want to use this so you don’t mess anything up. You could, of course, use a serializable thing, too. But most people would probably just benefit from just a regular old recommitted lock hint in there.

That would probably be good enough for most scenarios. But, you know, as always, make sure that you’re testing for your actual use case, not for what I say. That might be OK here.

I don’t always know. So, you know, I have limited insights sometimes into exactly what is going to be the best thing for you locally. But you can do that for all sorts of triggers.

This is an example with an insert trigger. This is, of course, an update trigger that does nearly the same thing. And this is a delete trigger, which would probably come pretty close to doing exactly what we just did with that foreign key with the cascading delete, where we would delete from whatever got inserted into there.

Actually, I might want to do that with the deleted table. You know. Maybe I forgot to change that one when I copied and pasted it.

We’re going to leave that one on the editing room floor, though. That’s going to survive there. And I’m going to give myself 10 demerits. I’m going to go do some push-ups.

But anyway. Let’s just change that to deleted. The deleted. There we go. And we’re going to keep that.

We’re going to keep the as I in there. And now everything looks good. So anyway. Copy-paste errors aside, I’m pretty happy with this one. Though I might have to apologize for the length since we did go a little bit over the 10-minute mark. But anyway.

Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. I hope that you’ll forgive my copy and paste error. And I hope that you’ll continue watching despite the fact that there was an obvious oversight at the end of this video. But anyway.

Goodbye. Cruel World. Cruel World.

Going Further

Simulating WAITFOR In Scalar UDFs In SQL Server

Posted on December 6, 2024January 22, 2026 by Erik Darling

Simulating WAITFOR In Scalar UDFs In SQL Server

Thanks for watching!

Video Summary

In this video, I delve into an intriguing and somewhat tricky aspect of SQL Server functions: making them wait for a specific amount of time. Erik Darling from Darling Data explores how to implement such functionality within user-defined functions (UDFs), which isn’t straightforward due to the limitations imposed by SQL Server. However, with some clever workarounds and insights from Alexander Kuznetsov’s blog post, we can achieve this through while loops that essentially create a delay. I walk you through creating these functions and demonstrate their use in practical scenarios, showing how they can be used for various sneaky and interesting purposes. If you’re curious about the full extent of what can be done with such functions, mark your calendars for Seattle to attend Past Data Summit where we’ll dive deeper into these and other advanced SQL Server techniques!

Full Transcript

Erik Darling here with Darling Data. In this video, we’re going to talk about something exceedingly tricky that you can do. It has to do with having functions wait for a specific amount of time. We’re going to talk more about this. It’s really hard to do a pithy intro here. But we’re going to talk about it. But before we do, we have some things to talk about. Like this channel. If you would like to sign up for a membership to this channel for as low as $4 a month, you can do that by clicking the Become a Member link in the video description. If you don’t have $4, even for one month, perhaps that just cuts into the ramen budget a little too heavily. You can do all sorts of wonderful free things that let me know you care. You can like my videos. You can comment. on my videos. And you can subscribe to the channel. I do like seeing all those things. It brings me a very specific type of joy. If you need help with your SQL Server, probably not anything that we’re going to be talking about today, but I am a consultant and I do some things with SQL Server very well. I don’t set up availability groups. I don’t really sit there and mind your backups. I don’t want to talk about capital R replication. But I can tell you, if your SQL Server is healthy, I can tell you if your SQL Server is as fast as it could be. The answer is no. And I can do all sorts of other things like make it faster. I don’t know. Some people enjoy that. Some people prefer that.

I can even reduce your cloud bills. How about that for a sales pitch? You want to give less money to Microsoft or Amazon? Call me. We can do that together. If you need some high quality, low cost training, you can get all 24 hours of mine for about $150 USD by going to that link up there and then using the discount code springcleaning. There is, of course, a link to automate all of that wonderfulness in the video description as well. So, and probably at this point, this might be past Data Summit. I don’t know. Maybe a little bit before, but you can still catch me there, November 4th and 5th. If it is past November 4th and 5th and you didn’t go to Seattle to pass Data Summit, you missed it. Sorry. Can’t do anything to help you there. But runner-up prize is if there is a SQL Saturday or Data Saturday or whatever Saturday event near you that is in search of a pre-con speaker, let me know. I will do my best to get pre-coned there.

But with all that out of the way, let’s talk about what I want to talk about, which is how you can get a function to wait for you. So, I realize that the logic in this function is not complete. Right. It just says, if delay is greater than zero, do this thing. Otherwise, we’re going to have sort of whatever in there. We could, of course, fix that with like, you know, putting 0000000 in there.

And then it would only change if delay was greater than zero. Otherwise, we would wait for zero seconds. Maybe that is enough. Actually, this function is now Turing complete. We’ve done it. Good job, us. But the problem is that if you try to do this in a scalar UDF, we’ll get an error message. It’s saying the invalid use of a side-effecting operator wait for within a function. That’s no good, is it?

We seem to have hit a wall here. Hmm. What can we do? What can the clever and devious mind do?

Well, a very clever and devious mind, long before I started thinking about this, actually had an example of what you can do. Smart guy named Alexander Kuznetsov. Kuznetsov. Something else. Probably I was pretty close on both of those. Left SQL Server for Postgres around 2013 or 14. Hasn’t been heard from since. Just kidding. He’s doing his thing.

But actually, maybe now that Pass has a Postgres corner, he’ll be back. I would love to give him a very big hug, probably out of nowhere and terrify him. But anyway, a long time ago, he wrote a blog post about scalar UDFs and he actually did the hard work for me.

And all I had to do was find a link to the hard work because everything is in the Wayback Machine now. But what you can do in a function is a while loop that for a, you know, you declare all this stuff and you set a delay in the function input. And this is just the default. You can change this, of course, when it actually runs.

But then you say while the current date time is less than that thing, you just, you know, run this stupid loop thing. And all you’re doing is setting a bit to null over and over again. So it’s very, very little work. But we can test that out and we can say, let’s just make sure this function is actually in there and created.

Sometimes weird things happen. Who knows? Who knows SQL Server? But if we say wait for three seconds here and we keep an eye on the clock that’s sort of next to me over here, you’ll see that that waited for exactly three seconds and then returned our column, right?

That’s pretty cool. And we can also have that, we can also have that act as input from a select list. So if I say select one, union all, select two, union all, select three, this function will wait for one second and then two seconds and then three seconds.

And we can, we can actually test that out by running this. Did I run that? No, I didn’t. I just hit R. Good job. Finger was off by one.

But don’t worry, this function will run for exactly, sorry, six seconds, right? To return those three rows. Now what can you do with something like this? All sorts of interesting, sneaky, outrageous things.

But you’re going to have to come to Seattle. You’re going to have to come to Past Data Summit in order to see all of those sneaky, outrageous things in action. So I suggest you buy your plane tickets now because it’s getting kind of late in the day.

It’s time to boogie. So anyway, thank you for watching. I hope you learned something.

I hope that you will be titillated to the point of travel by what I’ve discussed here. And you’ll be looking forward to seeing just how many awful things you can do with a function like this in SQL Server. Because trust me, there’s a lot.

Anyway, thank you for watching.

Going Further

How To Use A Numbers Table To Replace WHILE Loops In SQL Server Functions

Posted on December 5, 2024January 22, 2026 by Erik Darling

How To Use A Numbers Table To Replace WHILE Loops In SQL Server Functions

Thanks for watching!

Video Summary

In this video, I dive into the world of scalar UDFs that contain while loops—something I frequently encounter when working with older applications on SQL Server. These functions, often used in legacy systems from a time when developers didn’t fully grasp set-based thinking, can wreak havoc on performance as databases grow and evolve. To address this issue, I demonstrate how to rewrite these functions using numbers tables or tally CTEs, which allow for more efficient set-based logic that significantly improves query performance. By sharing practical examples and tips, I aim to help you tackle similar challenges in your own projects. Whether you’re a seasoned SQL Server professional or just starting out, this video offers valuable insights into optimizing function performance through modern techniques.

Full Transcript

Erik Darling here with Darling Data. And continuing with this week’s theme where I will no longer have to apologize for the length of my videos, we’re going to have another short one about how to rewrite functions with while loops in them. Because this is something that I end up having to do a lot for clients who have, you know, these, you know, I don’t know, 10, 15, 20, 25 year old applications. that have been on SQL Server since it came on floppy disks. And back then, people just didn’t know any better. I guess data was all small and easy enough that, you know, functions didn’t really do much of anything. And, you know, now that their databases are all growns up, performance is a wreck and scalar UDFs are often the case and cause for why. And we’re going to, we’re going to talk about that. So, if you want to join, like 20 other people who have memberships to this channel, click the link in the video description, it’s like four bucks a month on the low end and like 10 bucks a month on the high end, you can, you could spend that money in much worse ways. But I understand not everyone is charitable. And with the holiday season coming up and inflation being what it is, if you have to choose between buying your kids a new sock and giving me four bucks, well, I understand.

If you don’t feel like doing that, like, comment, subscribe. It’s all fun. Especially the commenting part makes me feel a bit less lonely. Of course, the comments are usually where I have to apologize for the length of my videos. If you need any SQL Server consulting help, this is the stuff that I’m great at. If you need anything else, I can probably do that too. And as always, my rates are reasonable. If you want some very low cost, very high quality training, you can get about 24 hours of it for about 150 USD at that link with the combination of that link and that, that coupon code, which is also available in the video description.

It’s your lucky day, I suppose. This November 4th and 5th, I will be doing full day pre-cons with Kendra Little at Past Data Summit. That is coming up very soon and it’s going to be a lot of fun. And I hope to have lots of interesting content from Past Data Summit for you. Live! Coming to you live from Past Data Summit. Anyway, let us get on with our party here.

Let us not accidentally click one more time and fade to black like amateurs. Let’s make sure we do this right. Now, a lot of what I end up having to fix for clients with scalar UDFs is some kind of, I mean, it could be a multi-statement table diode function too. They both have sort of the same problems when it comes to wrecking query performance.

You know, a lot of the, a lot of the multi-statement table diode function ones that I have to fix are string splitters, which are, you know, always a fun challenge. Some of them are string concatenators. There’s all sorts of fun things that happen when, when you start getting into that stuff and the, and the particulars of that.

But, you know, a lot of it too is, you know, stuff like this. So I’m just going to actually just show you because it makes things easier. In this case, you know, like a lot of these functions might be used to like strip characters out.

And if you go to my GitHub repo, which there’s a link to it somewhere, I’m sure, you’ll find some functions from me that can strip letters, strip numbers, or match a pattern and strip that pattern out. So there’s a few good things there.

But this is just kind of an abridged version of that where, you know, there will be some function from the, you know, from the beginning of SQL Server time that has a while loop in it. And it’ll iterate over every, you know, character in a string and, you know, figure out if the character in the string matches some pattern or whatever.

And, you know, back in the day, a lot of people found that a very easy and approachable way to do things. Because, you know, not a lot of people were hip to the whole, you know, databases, think in sets, don’t write procedural code and functions and then expect it to perform well thing.

You know, there were a lot of people who were missing that part of their brain because it hadn’t been invented yet. It was just an empty space in their head where they were just like, oh, we’ll get a part in here someday. But, you know, here’s the result of the function.

And I just want to point out, if you’re ever, like, rewriting functions, this is the wrong way to benchmark them. Because, you know, if you’re just, you know, fixing one single string, you’re not going to see an appreciable difference. You really want to incorporate the function into the queries that are calling it.

Not just, like, you know, do stuff like this to, like, unit test it. Test it for correctness. But this isn’t a good performance test right here. We’re not going to do a performance test here because I’ve done a million function videos about performance testing.

This is just an example of how you can get out of, like, how you can rewrite functions with while loops in them so that they are less awful. So if we run this, we will get a count of five and five. One for the count of the letters, which is, we’re saying, where everything is not a number zero through nine.

And then one that is a count of the numbers where everything is a number zero through nine. And, of course, we’re using pat index to do that pattern matching. Because what else would you do to match patterns if set views at the pattern index function?

I don’t know. I don’t know. You’re crazy. But, like, an easy way of doing it is to get a numbers table involved in your database. It does not have to be a particularly large one.

It does not have to be a bajillion rows. You could have a simple, you know, like, 10,000, 20,000, maybe even 100,000 row numbers table of just the numbers one through 100,000. And you could go a really long way with doing this sort of thing, right?

You can do a lot of, you can get through a lot of characters with a 100,000 row numbers table, specifically about 100,000 characters. If you have strings longer than that in your database, obviously, you’re going to have to compensate for them somehow. But with a larger numbers table, perhaps.

But, you know, for most people, having more than 100,000 characters that you would have to do this sort of thing on is perhaps a bit much. So what we’re going to do is we’re going to use a numbers table to our advantage. And we’re going to use this function to completely replace the while loop that we had before.

And let’s just make sure that this thing is in there. And, of course, I’m going to write the query that calls this a little bit differently, where I’m going to put the patterns that I care about in this values clause and then cross-apply the function with the values of the values clause over here.

And we will get, excuse me, for each of these, we will get the correct answer. So for the ones that are numbers, we had five. The ones that are not numbers, we had five.

And, of course, these things both finished instantly, just working on a single string. Pay no attention to what you saw down there. That was something else that I was working on for a different demo for a different day. Pretend that didn’t happen.

All right. It’s our secret. Doesn’t it make you feel special that now you and me have a secret together? You’re honor-bound until death to keep that secret, you and me.

So no tattling, as they say. Anyway, if you find yourself having to rewrite functions that have while loops in them, numbers tables are very good for that.

There are great examples of trading numbers out there in the universe. I believe Aaron Bertrand has a bunch of posts about it. There are a lot of people who use them for a lot of good things. They’re not always an awesome performance boon when used directly in queries, sort of like date tables and date dimension tables or whatever.

They can be very useful for things, but they can also be involved in weird performance issues because joining to those is often kind of awkward. But in this case, where we just have to use that numbers table as a sort of utility, like fake row number type thing, it’s pretty easy to do this here.

If you’re not allowed to create a numbers table in your database, you can, of course, use CTE and nest those to create a large row set of the numbers, one through whatever, in order to do this instead.

This just makes the code a lot nicer and a lot more compact. So this is one way that I help people when we need to rewrite functions with while loops in them by using either a numbers table or a tally CTE, as it’s sometimes called, so that we can do what we need to do with set-based logic rather than with procedural looping logic because that usually helps query performance a whole lot.

Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. I hope that this video was a satisfactory length for everyone. Not too big.

Not too small. It’s the Goldilocks length of the video. Because I don’t think Goldilocks ever had to apologize for her length. But, I don’t know. That’s a different sort of fairy tale.

Anyway, thank you for watching.

Going Further

Finding Bad Density Vector Estimates In SQL Server

Posted on December 4, 2024January 22, 2026 by Erik Darling

Finding Bad Density Vector Estimates In SQL Server

Thanks for watching!

Video Summary

In this video, I share a script that I use in the Stack Overflow demo database to identify columns where density vector estimates might be inaccurate due to local variables. As a SQL Server consultant and trainer, I often need to create compelling demos to illustrate common issues like bad cardinality estimates, particularly those arising from local variables in WHERE clauses. This script helps me pinpoint such problematic columns, ensuring that my demonstrations are both accurate and effective. If you’re interested in supporting this channel or getting high-quality, low-cost SQL Server performance tuning content, consider becoming a member for as little as $4 a month—there’s a link in the video description to join now.

Full Transcript

Erik Darling here with Darling Data. I’m going to remember to flip the switch on my thing this time and we’re going to get started. I had a whole funny thing to say, but that took the, took really took the wind out of the sails there. So screw it. We’re just going to keep going. In this video, I’m going to show you and share with you a script that that I use locally in the Stack Overflow demo database to find columns where the density vector estimate would not be good. And the reason why I, I had to, I did this is because, you know, I, I am a SQL Server consultant, trainer extraordinaire, and I need to come up with good demos. And sometimes when, you know, I need to either show clients or I need to put a link in the description, put together training about things that cause bad cardinality estimates, probably the most common one that I see, you know, aside from like table variables or, you know, non-strangable predicates is when people use local variables in their where clauses. I’m not going to go into all that because I’ve got a post about that. If, if you are just so anxious to see that post, it’s on my website, erikdarling.com. The title of the post is yet another post about local variables. There will be a link to that in the description.

video description, just in case you have some contrary urges to Googling or whatever that. And, you know, I have to come up with stuff that proves out my point that, you know, for the gen in general, local variables are not a best practice to replace parameters with. You don’t want to fix parameter sniffing that way. It’s not, you won’t have a good time. And so I wrote this script to do that. But before we go look at the magnificent majesty that is that script, let’s talk about how you and I can get closer. If you would like a membership to this channel for as low as $4 a month, you can click the link that says like join now or something in the video description. And that should bring you right to where you need to go. So, uh, as usual, all of this content is free of course. Uh, and if you just want it to, you know, keep leeching off my hard work, uh, you could at least like comment and subscribe so that, uh, I feel a little bit less lonely in this crazy mixed up world. Uh, uh, I don’t know. That’s good enough there. Uh, if you need SQL Server help, if you’re in the market for consulting, I am pretty good at all of these things.

Actually, I’m very good at all of these things. I’m better than pretty good, very good at all these things. And, uh, you know, if you, if you, if you decide to do any of this with me, you don’t have to do the other stuff. Uh, if you would like some high quality, low cost SQL Server performance tuning content, well, what do you know? As a SQL Server consultant is noted by the last slide. And trainer extraordinaire is noted by this slide. You can get all of mine for life for a 75% off, which means about 150 USD at the end of the day. Uh, discount code there link up there.

Of course, all of that in the video description. Uh, I don’t know how many more of these videos I’m going to actually have this information in. Cause at this point I sort of forget where I have these scheduled out on the blog and where I have these scheduled out in the month. So, um, November 4th and 5th past day to summit me and Kendra little two days of performance tuning, train, performance tuning, pre-cons, not performance tuning train wrecks.

Uh, I mean, you, you have the train wrecks. We have the performance tuning. Uh, so that’s, that’ll be fun. Um, uh, hopefully by the time you watch this video, there’s still time to buy a ticket. Okay. So with that out of the way, let’s get on with the show and look at this fantastic script that I wrote. Um, I forget when I wrote it, but anyway. Um, so full caveat here, I, I run this script specific to the stack overflow database.

And because I’m not starting with any statistics, uh, I actually drop all the statistics and indexes to, before I run this, I have to do some initial stuff in here to create, uh, statistics on all of the columns that I care about. So I have some preamble stuff in here that will create statistics on everything. Please review the script carefully. If you already, if you already working with a database, you’re not going to want to, uh, run drop indexes to drop indexes and statistics. You probably hopefully don’t even have that installed on your production server slash database. Uh, and then you’re also going to want to skip the part that does the create statistics stuff because you don’t need to create a whole bunch of extra statistics in there.

Um, so that’s the first part that kind of goes and does that. And then after the statistics, statistics get created, boy, oh boy, we’re having a great tongue day today. Aren’t we? I just flip all over this thing. Uh, I create a few tables to hold the output of DBCC commands. Um, I know that there are built in DMVs and DMFs that do some of the stats stuff now, but, uh, I like the way that these things work a little bit better. And the way that they, you know, some of the information that they give a little bit better. So I stick with the old fashioned DBCC commands.

And, uh, then I go and I cursor over the statistics that I care about and I run, um, the DBCC show statistics with stat header. And I put that in a table and then I have to do some updates to make sure that I have the right stats names and stuff in there. Uh, and then I, oops, uh, I hit the wrong button outside of the VM. And that looked funny. That looked funny locally. You probably didn’t see anything. Uh, and then I do the same thing, uh, for the density vector part of DBC show statistics.

And then I do the same thing with the histogram. So I get three different DBCC show statistics components separately. And I put those into table variables because performance does not matter here. I can use table variables. It’s wonderful. But then I take all that stuff and I put all of that into a temp table and then, um, right there.

And so that’s the results of the header and the vector and the histogram. And then I run some queries to show me what comes out of that. Now I’ve already run this, so you don’t have to do most of it, but, um, there’s, there’s a few different results in here. And the one where I found really the best, um, the best demos from, and if you’ve ever watched my videos, you might recognize some of these.

This first result shows me where the, um, the guess that I would get from an equality predicate wildly messes up how many rows would actually come back from that. So, uh, this chunk in here, uh, of course, most of it on the post table, but this was all really good. Um, this one here on the votes table, uh, was good for, well, I mean, the user ID column in the votes table is all null.

So, uh, this one was questionable at best, but, uh, you know, figuring this sort of stuff out, uh, in the script was a little bit, uh, you know, a little bit more difficult than I would probably want to get into. But, uh, this top one up here on parent ID, uh, in the post table where, um, SQL Server guesses 120 rows, um, but we get 6 million rows back. That was a very, very good one.

Some of the ones for nulls are good for showing different stuff. Like if someone compiles a store procedure with a null parameter, that was good for something different than the local variable stuff. But, um, the, the local variable guest stuff for especially the parent ID one, uh, and the accepted answer ID one, those were excellent.

Uh, and those have spawned a lot of great demos. So, um, I don’t, I don’t know who is going to be interested in this code. I don’t know if it’s going to be maybe someone who also has to write demos for SQL Server.

Um, maybe you have a demo database where you want to figure this stuff out. Um, you could do the same thing there, or maybe you, in your database, you know, maybe you have the code with a lot of local variables and stuff in it. Then you want to figure out maybe where, um, you know, your local variables might be causing bad cardinality estimation and performance problems.

You could do that with this if you wanted. Um, quite frankly, if I were trying to find poorly performing code, I probably wouldn’t start with this. I would probably just start to find queries that have a high CPU and or duration.

Then I would try to figure out if local variables or bad cardinality estimates or whatever else are the cause of that. So, um, really this is probably mostly a tool for presenters who want to find good demos. Um, I wouldn’t recommend, again, running this in production to do anything because, um, I don’t want to be responsible for whatever happens.

In there from doing all this, right? Running those unlicensed DBCC commands. Anyway, uh, just sort of a fun video with a fun script.

Again, this will be on GitHub. This will also be a link in the, in the video description. Um, if you feel like giving it a spin in your demo database or your non-production database, uh, it might be fun for you. Just remember, if you’re running this in an actual database, you’re going to want to skip the create statistics part because, um, you’ll, you might spend a very long time creating statistics on a bunch of columns so that you, you have things to look at.

Uh, but demo database wise, this is a lot of fun. Anyway, um, I don’t, I don’t know. You know, they, they, they, they can all be in-depth SQL Server performance tuning stuff.

Sometimes people like to see how the sausage gets made. And at Darling Data, we make a lot of sausage. All right.

Recently voted by Beer Gut Magazine to be the, the sausage king of SQL Server. So, got a lot going for us here at Darling Data. Anyway, thank you for watching.

Going Further

Performance Pains With NOT IN And NULLable Columns In SQL Server

Posted on December 3, 2024January 22, 2026 by Erik Darling

Performance Pains With NOT IN And NULLable Columns In SQL Server

Thanks for watching!

Video Summary

In this video, I delve into the performance pitfalls of using NOT IN and nullable columns in SQL Server queries. Erik Darling from Darling Data shares his expertise on why you should be cautious when employing NOT IN, especially when dealing with null values, as it can lead to unexpected results and complex execution plans. I explain how using NOT EXISTS instead can simplify your queries and avoid the performance overhead associated with NOT IN, making your SQL code more efficient and reliable.

Full Transcript

Erik Darling here with Darling Data, the most punch drunk SQL Server consultancy on the planet. Anyway, some days we’re just tired. SQL Server is exhausting, isn’t it? It’s like, man who thought he couldn’t possibly get any more tired got way more tired. Anyway, in today’s video, we’re going to talk about a performance peril with NOTIN and Nullable columns. Now, if you’ve been working with SQL Server for, or databases in general, for like longer than 15 seconds, you’ve probably run into someone on the internet saying if you use NOTIN and you hit a null, your query will bail out and return no results, or return results that you wouldn’t expect. And you should use something else to do, to write your query, or do something else to do. something to handle the nulls, right? Is null, coalesce, something, you know, some, some other crappy idea. In this video, I’m going to talk to you about, like, not only like how logically that works, but also from a performance perspective, how even if you don’t hit that situation, SQL Server has to come up with some really weird execution plans to protect itself when you do run into that. So with that, that, that intro complete, I suppose this is where I beg you for beg you for your, your, your, your alms for the poor. If you appreciate my channel content, and you would like to donate $4 a month to keeping Erik Darling alive and somewhat well fed, you can do that by clicking the link down in the video description. It says become a member. You can become a member. It’ll cost you $4 a month. If you don’t have $4 a month, well, if you’re just some freeloading, weird, I don’t know, college student or something, you can like, you can comment, you can subscribe.

You know, whatever floats your boat, strikes your fancy, blows your hair back, keeps your powder dry, whatever, however you, however it makes you feel, you can do those things. If you need help with SQL Server, if you need consulting, well, golly and gosh, you found a young, handsome consultant with reasonable rates right here on YouTube. You can get in touch with me. I can do all this stuff. I can do more stuff. This is just the stuff that I really like doing. So if you have something else, well, I don’t know, suppose we can talk about that. My rates will remain reasonable. If you would like some high quality, low cost SQL Server training that lasts for as long as you live, you can go to that link and then you can enter that code and then you can get all of mine for about $150. It’s a pretty good deal.

If you want to catch me live and in person, I will be at Past Data Summit. November 4th and 5th, I will be delivering pre-cons. Of course, the Past Data Summit itself goes on much longer than that. I even have a regular session on isolation levels. It’s really going to twist your melon, bake your noodle, you know, all that stuff. So you should come to that and you should see me live because it’s even better in person. I promise.

So with that mundane nonsense out of the way, let’s talk about not in and the problems that it can cause. All right. So I’m going to run this, not that it does anything, and I’m going to run a couple of funny looking queries. It’s very, the reason why they’re funny looking and the reason they might strike you as funny looking is not because they’re standing next to me, but because there is no from clause in these.

All right. It’s just select one, X equals one, where select zero is not in one or two, and then there’s select one, where select zero is not in one, select null. Right. I guess there’s a union all in there, too, for good measure. But when we run these, this is sort of what I was talking about from a logical standpoint, where if you have a null, SQL Server is like, I don’t know, can’t possibly figure that out.

You get a blank result rather than what you would expect, which is this. Right. You would expect to see a one come back because zero is not in one or null. Zero is also not in one or two, but we got a result back from that one, whereas this one we didn’t.

Right. So that’s not that’s not a very good time. Now, the point of this video is not that. Right. That is obviously a logical sort of I mean, I’m not going to call it an inconsistency, but it is it is sort of strange.

Right. Because in you have no problem there. So if I were to give you a general rule of thumb, if you’re going to use in or not in, then you should do that only when you have literal values that you write into your in or not in.

Right. Whether there are numbers, strings, dates, something like that. If you are typing the actual values you care about in in or not in, then you’re probably all right. For not in, you do have to pay attention to if the column in the table is nullable or not.

But, you know, that that’s up to you to figure out. But if you need to supply a sub query or anything like that, then you really should be using exists or not exists, because you will you do not have to deal with the same sort of weirdness that you end up with when you use in and not in or rather specifically not in.

But what I what I did is what I’m going to do here is I’m going to prep a couple of tables and I’m going to stop there. Well, I’m going to run that and then I’m going to come over to the query plan. So this is the not in version of the query plan right here.

Right. So this is the thing that I ran with not in after populating these. The thing that you’ll notice is that for both of these tables, these columns have been declared as nullable. Right. This this column is nullable.

Where’s that other one? There it is. This column is nullable. But we have specifically put a whole bunch of not null values into both of these tables. Right. We’ve only put values into the comment table or user IDs from the comment table where user ID is not null.

And we have only put the owner user IDs from the post table where owner user ID is not null. So we don’t have any nulls in there, but the columns are both nullable. And since we define the table that way, that can happen under all sorts of other circumstances, too.

If your column is nullable and you do select into or you create a table and you don’t specify null or not null, you might be surprised at what what the actual table definition is. But this was the query plan that that ran for the not in version.

Right. You can see the not in up here. Select this stuff. That is exactly what I showed you in the other tab. This thing runs for a little over 13 minutes.

For that little over 13 minutes, you’re going to notice some extra stuff. Right. So the query itself, of course, we are just saying from this. Where this column. I didn’t frame that up well at all, did I from this where this column is not in select this column.

Right. That’s all we got there. Right. But we look at the query plan. We see comment post comment comment.

We have three references to comment and one reference one one reference to post. Now, what ends up happening is SQL Server has to do a whole lot of gymnastics to figure out which columns might be nullable or might not be. So SQL Server uses this row count spool over here to start figuring out if any if any rows in the comment table are null.

And it has to count all of these to figure out if it’s going to just bail and give you nonsense. Then we also have SQL Server doing all this crazy stuff where for 13 minutes we have a top above a scan where SQL Server just keeps going in here and doing an anti semi join to the post table to figure out some of the not in. Right. But when we do this, this is when we’re also trying to figure out if there’s any nulls in there that might mess things up.

And then finally, way over here, we have the result of all this work right anti semi joined to the base table itself. So SQL Server had a real tough time and did a whole lot of extra work trying to figure out if there were any nulls in there. Like what’s going to happen? Should I bail early?

Like like like can we even fit? Can we figure this query out? That doesn’t happen with with not exists. So the not exists version of the query and this is, you know, something that when I first started writing SQL, I messed up because I, you know, I was like, holy crap. In and not in. Oh, man. Stuff is weird with them. Like not fully knowing the whole story.

So I just started taking in and not in and writing like exists and not exists. But I would always forget this part of the exists, like the correlating part. So it would just be like we’re not in select something from this table.

And then that that that doesn’t work. Right. Because with exists and not exists, you don’t project out anything from the select list. That’s why I can stick one divided by zero in the select list and nothing happens.

I don’t get an error because SQL Server doesn’t evaluate this. Right. You can put whatever you want in there except an aggregate. It doesn’t matter.

A lot of people think that if you put top and it exists or not exists, it’s somehow faster. It’s not. There is an implied top in there already. Ah, crazy. Anyway, it’s called a row goal.

You should learn about it. When I run this query and say select count from whatever where not exists, this thing, oops, I didn’t turn on query plans. This does not take 13 minutes to run.

This runs pretty quickly. And we just have one simple hash join, a right anti-semi join, which from the comments table to the post table. And we don’t have all that extra work.

We don’t have that row count spool where SQL Server is trying to figure out if there are any nulls in there. We don’t have that weird top above a scan where SQL Server is trying to figure out if it can keep getting rows. If like, should I bother?

Should I bother? Should I bother? What’s this? What’s this? What’s this? Pass it along over here. We don’t have all that. SQL Server just takes care of all this with the not exists in one simple join. And this finishes a lot quicker, right?

It doesn’t take 13 minutes. It takes 1.7 seconds. So when you’re writing queries, do be very careful with not in. Do make sure that if you are going to use not in, like I said, sort of a general rule of thumb, use literal values. Make sure the column that you care about is not nullable.

And, you know, if you are unsure, if you just want to be safe in general, exists and not exists are a far easier way to deal with subqueries and other stuff like that. Because you don’t have all these sort of execution plan oddities that you can end up with when you start using not in for these things. So my general preference is exists and not exists.

But as usual, or, you know, this is your first run around the block. Don’t forget that correlation in the exists or not exists. Otherwise, you could be very surprised by the results.

They are. They might be shocking. You might find that you have found everything or you have found nothing. Right?

Go figure there. Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. I hope that you will avoid nullable columns. I hope you will avoid nulls. And especially mixing all of that stuff with not in because the results you get will not be happy ones.

All right. Anyway, thank you for watching. Goodbye.

Have a nice day. I hope it’s the best day of your life. Which is secretly, I guess, a curse because every day after that would just pale in comparison. Huh.

Well, maybe you live to be a thousand in interesting times. Life’s funny.