Tag: Query Tuning

How To Write SQL Server Queries Correctly: Views vs Common Table Expressions

---

Thanks for watching!

Video Summary

In this video, I dive deep into the world of views and Common Table Expressions (CTEs) in SQL Server, addressing some common misconceptions and providing practical insights. Erik Darling from Darling Data shares his experiences and observations on how these objects are often misused or misunderstood by developers. Whether you’re a seasoned DBA or just starting out, this video offers valuable perspectives on when to use views versus CTEs, the importance of avoiding materialization issues, and the potential pitfalls of using `TOP` in views. I also explore why there seems to be a bias against views among some developers while CTEs are often embraced without question. By the end of the video, you’ll have a clearer understanding of how to leverage these objects effectively for better query performance and maintainability.

Full Transcript

Erik Darling here with Darling Data. And in today’s video, we’re going to talk about two of my frenemies in SQL Server, views and CTE. There are some things to discuss with the views and CTE that I think are important for people to know. And we’ll do that today. in some level of detail that will be too much for some and too little for others. But guess what? It’s a free video. I’m doing what makes me happy. So if you are also made happy by this video, and gosh, I hope you are, you can become a member of this channel for as low as four, that is, quattro dollars a month. there’s a link in the video description somewhere in this general vicinity.

You can click on that, become a member, and that’d be cool. It’d be very kind and giving of you in the holiday spirit. If you like to show your holiday spirit in different ways, different holly jolly ways, you can like, you can comment, you can subscribe.

That’s all right, too. If you are watching this video, it’s like, I mean, I know this is going to get published the day after Christmas, but who knows when you’re watching it.

But let’s say you’re like, wow, we have all this New Year’s budget. How are we going to spend it? What about on your SQL Server with spending some quality time with Erik Darling from Darling Data? You can hire me to do all of these things.

And I do them the best in the world, because I’ve seen what other people do, and it sucks. If you would like some very high quality, very low cost, SQL Server performance tuning content, again, there is a link in the description below where you can get all of this stuff combined for you.

But it is about 150 USD for the whole thing, and that is for the rest of your life. So live a long time.

Be happy. Again, this is being recorded, well, this is being presented at the day after Christmas, so I’m going to be huddled up somewhere, probably drinking red wine and staring dreamily as my children ungratefully unwrap presents in Paris.

So, no, that’s my plan. I don’t know what you’re up to. And with that out of the way, let’s have fun.

Let’s talk about CTE and views, because I suppose that is what we came here for, isn’t it? So one of the most exhausting parts of my job is, you know, being like the groundhog DBA who has to sort of say the same thing to different people over and over again.

It’s like starting from scratch. It’s like, I get really good at the piano, but the world around me is just starting from scratch, and no one realizes how good I am at piano.

So there are a lot of notions that people have about SQL Server that are both untrue and untested by them. It’s like, oh, I thought I read a thing that said that once.

I’m like, oh, can you show me the thing? No, of course not. It’s all in the sands of time. But the way that I think about views and CTE are since when you make a view, you write create or alter view, and then you put the query in it, which hopefully doesn’t contain too many other views, then hopefully the view definition doesn’t contain anything too outrageously awful, because Lord knows they have that.

People have a propensity for putting all the worst things into their views. They are like a permanent home for your query. They are not a permanent home for your data, because your views are not self-materializing.

And you have to go through great troubles and lengths that Microsoft should apologize for in order to create an index view. But it’s a thing that lives in the definition of your database.

It is not a physical object, but it’s like a stored procedure doesn’t materialize the data that the stored procedure selects and does stuff with.

View doesn’t either. But CTE are a bit more like mobile homes, because you can take one and you can park it anywhere, and it doesn’t actually live there.

You can put it over here. You can put it in a stored procedure over here. You can put it in a stored procedure over here. You can write it wherever you want. Just pull up, park it, have it make performance suck there too. It’s sort of like installing a toilet, right?

There’s a time and a place for a toilet, usually in the bathroom. If you use the same amount of discretion with toilet installetry that you use with your views and CTE, and you say you plop it right in the middle of the kitchen, probably don’t hook any pipes up to it, just leave it there, it’s going to look stupid immediately, and eventually it’s going to stink in your kitchen.

Probably not what you want next to the dinner table, is it? Unless… Unless… Oh, I don’t even want to go down that path.

Since views are programmable objects, right? They are actually modules in your database. They do have a little bit more depth of character and flavor to them than CTE.

For example, like when I talked about views in another video, you can add a with check option to have SQL Server do stuff with the data in the view when you modify it. You can also index a view.

You cannot index a CTE. Contrary to what I’ve heard said at several live in-person events and read in several places, views in CTE actually can use the indexes on the underlying tables that they select from.

The data does not become an amorphous blob anywhere. You can use the indexes there. It’s pretty spiffy.

Yeah. All right. What I find particularly curious about the view and CTE thing is how developers are sort of racist against views in a way that they are not against CTE.

So like, like I’m pretty sure that this is just like a random conversation that has happened 5 million times in the world. One developer will be like, just wrote this short procedure.

It has hundreds of CTE in it. And another developer will be like, wow, that’s amazing. You’re the best at SQL. These CTE, dog, they’re so readable.

I can really, really understand all this query. I really can’t. And then if the developer did the same thing, was like, hey, this database has hundreds of views in it. Developer will be like, man, why, why, why, why you gotta like, you know, mess up the database with all these views?

What’s wrong with you? That just makes things hard and complicated and unreadable. So like, it is weird that despite them having so much in common, and despite views having a leg up on CTE in several ways, you know, people are sort of aligned against them.

But, you know, even I kind of get it because I cringe a little bit when I see that, when like, someone’s like, I don’t know, I have this simple query, but it takes forever.

And I’m like, oh, okay. And it’s just like, you know, select stuff from a thing. And then you’re like, oh, well, I bet it’s just missing an index. And so like, you go to get the estimated plan, and it’s like, see what’s going on.

And then the estimated plan is like, gigantic, like, like one of those like open world video games where the map just keeps getting bigger and bigger and like, fill, and then that’s the query plan.

And like, you have to like zoom all the way out to even be able to grasp the full size of it. So, so I understand because views have been abused so horribly, but, but, but, but, but, you know, by the same token, CTE in my experience have been abused, just as horribly by people.

It’s just easier to see upfront and it’s not more readable. One thing that I see quite a bit is people still trying to stick top 100% in a view, thinking that it will present their data in order when they select from it.

It won’t. You need the outer order by no matter what. But one thing that I want to show you is like, if let’s say that we have a select top one in a view, and then we have a select top 100% in a view, if I show you, I’m not going to get the actual plans for these because this one will select 100% of the 2.4 million rows out of the users table.

And I don’t want to sit here for that. But if I show you the estimated plans for these, you’ll notice something, a slight difference between them.

The first one has a top operator in it, because there is a top that is actually honored by the optimizer. The second one does not have a top operator in it. The optimizer throws top 100% away, because top 100% means the whole damn table.

That does not mean there is no need to do a top operator in there, because we are selecting everything. If we replace this with top 99%, well, that’s a huss of a different color, because SQL Server actually has to figure out 99%.

It just has to do it in a real ugly way. You don’t want SQL Server to scan the entire clustered index, spool the entire clustered index, 99% of the clustered index into an eager table spool, and then have the top read 99% of the rows from there.

That’s a bad time. So we’re going to say that’s a bad idea. We’re going to not go with that idea. I’m going to change that back, because I don’t want that to accidentally be there and have anyone…

I don’t want to, like, die and have anyone go through my database and be like, he had a view with top 99% in it. Glad he’s dead.

I don’t want that to happen. So one other thing that’s important, and let me just get rid of that red squiggle down below. One thing that is important to understand about views and CTE, and this is something that I’ve said, a point that I have belabored, that this dead horse is well fed, that they don’t materialize nothing.

So when you reference a view, or… Excuse me. I’m very dry in here. This winter heat stuff just…

Despite there being a prevalence of steam pipes, no steam releases from the pipes. It’s just dry, dry heat. Because views and CTE do not materialize data, every time you reference them, or every time you access a view or CTE, the entire query inside it has to run.

I have written an unnecessarily large query in here, but if I go and create this, and I repeat that same thing, this is using a CTE, of course.

This is using the with syntax to create a CTE. And then I either join that CTE to itself, like so, or I join the view to itself, like so, and we look at the query plans.

We will see that the query plan for both of these, when it finally does a thing, will repeat itself for both of them. We have the set of joins from the first time we talked to our CTE up here, and we have the set of joins from the second time that we talked to our CTE down here.

We have the exact same pattern repeat in the view. It’s the same query plan. This is the first time we touched the view. This is the second time we touched the view. Every single one of those joins has to happen all over again.

It’s not a good time. And I see people do this constantly with both views and CTE where they’ll take the view. They, oh, all I need to do is run the view with like this where clause in a CTE, and then run the view with this where clause in this other CTE, and then join one to the other.

And you have this query plan that, again, just defies all logic. Well, I mean, it doesn’t defy logic because I know what’s going to happen, but it really, I think the better is, it defies like reasonable analysis because you’re just looking at this giant query plan and going, why would you do this in the first place?

Why would you want to hurt SQL Server like this? So not a lot of difference here. No matter which one you use, there is no materialization.

You can at least materialize a view. You can index a view. Again, a lot of rules to follow there. You cannot index a CTE, like the definition of it.

You can’t say like with CTE as, and like define an index in the CTE definition. But both of them, both views and CTE can use whatever underlying indexes you have.

They’re often responsible for either one being successful. But of course, the crappier code you put into either a view or a CTE, the worse off you are.

So neither one is going to turn out well. I don’t really know. Let’s see. Yeah, okay. Well, I mean, one other good point in here is that if you use views and you hire a young, handsome performance tuning consultant like myself, you take a look at my reasonable rates and you think, gosh, how can we afford not to?

And let’s say I come along and I performance tune a view. Everything that relies on that view will get faster. If you just sprinkle CTE everywhere, like kitchen toilets, guess what’s going to happen?

I’m going to have to go through every place you use that CTE or a CTE, and I’m going to have to fix all of those individually. You can imagine which one is a better use of time. So there is that to consider.

Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. I hope that you will practice better diligence in your use of both views and CTE.

And I also hope you keep watching. So let’s do all those things together. Go team.

I’m not much of a high five guy, but I don’t really have much of a… It wouldn’t make sense for me to do this. Like really for the camera, the high five or the fist bump is the only thing that makes any sense.

So anyway, let’s go record another video. We’re going to talk about, I guess, or and where clauses next. Ooh.

It’s going to be perf heavy topic, isn’t it? I’m going to have to get into that one. Anyway, thank you for watching. Goodbye.

I love you.

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.

How To Write SQL Server Queries Correctly: INTERSECT And EXCEPT

---

Thanks for watching!

Video Summary

In this video, I dive into the lesser-known but incredibly useful `INTERSECT` and `EXCEPT` operators in SQL. These operations are particularly handy for comparing columns across multiple queries without the need for verbose and error-prone null handling logic. By exploring these operators through practical examples, you’ll see how they can simplify complex comparisons and improve query performance. I also delve into their unique operator precedence rules and discuss when to use them effectively in your SQL queries. Whether you’re a seasoned database professional or just starting out, understanding `INTERSECT` and `EXCEPT` will give you a powerful toolset for writing more efficient and readable code. Additionally, I touch on the recent additions like `IS DISTINCT FROM` and `IS NOT DISTINCT FROM` in SQL Server 2022, highlighting how they further enhance T-SQL’s capabilities. By the end of this video, you’ll have a solid grasp on when and how to leverage these operators for your own projects.

Full Transcript

Erik Darling here with Darling Data. We’re going to do a great job today. I can just feel it. I’m feeling in my bones. And I can tell we’re going to do a great job because we’re talking about a subject that almost no one ever talks about. Intersect and accept. You might notice that that word looks a little funny. Brevity is the soul of wit, I think. So, you know, these are not things that I just want to say. I see people use in production queries terribly often. Most of the time when I see people use these, they’re trying to compare, like, the entire contents of one table to another and try to figure out where there are differences. You don’t see people use these a lot in, like, smart ways in queries. They would much rather sit there and, you know, figure out if columns match or are both no. Or if, like, like, like, like wrapping things in is null and coalesce like, like the fools they are and these giant or clauses. And, and gosh, gosh, is it nice to just retype those as, as intersect or accept queries because not only do they, they look prettier, but they often perform better. And there’s often a lot of, a lot of room and potential for logical incorrectness.

in, uh, extended and, uh, or clause queries, uh, that, you know, uh, usually you’ll find a couple few bugs in those. So, we’re gonna have fun today. But before we have fun, we need to talk about, uh, stuff you can buy from me. Yeah, cause everyone’s trying to sell you something. I guess that, that includes me. Merry Christmas. Uh, if you would like to become a member of this channel, uh, and, and, and support my, my, my, enduring efforts to bring you quality SQL Server content, uh, there’s a link right in the video description that says become a member in which you can become a member for as low as $4 a month.

If the $4 a month is just too rich for your blood, uh, you can like, you can comment, you can subscribe. There are all sorts of buttons you can push that push my joy buttons, which is just the, probably one of the, the best things you can do in life, right? Uh, what is it? It costs nothing to be kind. So, it says the sages of every social media platform overrun with people, uh, being selectively kind. Uh, if you need help with SQL Server, if you’re looking at your SQL Server and thinking, gosh, this thing is slow, uh, Erik Darling, uh, from Darling Data does all of these things, the best in the world.

Uh, so you can, you can, you can hire him, me, us, as, as a, as a package and get this handsome devil to show up and make your SQL Server faster in exchange for money. And as always, my rates are reasonable. Uh, if you would like to get some training on SQL Server, I have about 24, 25 hours of it. Uh, there is a discount code you can use to get 75% off that brings it to about 150 USD and you get that for the rest of your natural life.

Uh, again, it is the end of 2024. I might just take this slide out because I’m getting sick of saying it. 2025. But, you know, then if I, if I stop saying it, maybe you’ll forget that I, I, I come places and I speak and also in exchange for money. Uh, so if you would like me at your event, tell, tell me what your event is.

With that out of the way, let us intersect and accept ourselves into oblivion. Now, one of the first times I actually ever saw someone use, uh, these operators was, of course, uh, my, my dear friend and my, my wine distributor from New Zealand, uh, Paul White. And, uh, one thing I want to note is, uh, you see that URL up there? Um, it’s, uh, right, right, right about there.

Uh, I want you to just take a quick look at the date in here. 2011. So June of 2011. Uh, so there, originally I was going to put a trigger warning on this because, uh, you know, obviously code from, uh, gosh, almost 15 years ago, uh, does not live up to today’s modern standards of code formatting style choices.

Uh, but instead I have reformatted this code to fit modern conventions, coding standards in T-SQL. Um, I’ve also taken the liberty of replace, uh, in the original, uh, queries, the, the table, the temp tables were table variables. I’ve only, I haven’t changed those for any overarching performance reasons only to make each of the select queries below a little bit more portable.

Okay. Uh, because otherwise I would have to declare and populate and run the select queries for each of the things below. And that, that just gets a little unwieldy. So, uh, we’re going to, uh, just do this instead. Uh, we’re going to make our table variable, our temp tables, not table variables. Uh, and then we’re going to insert some data into them.

Um, and then, uh, so the, the cool thing about Paul’s post is that he walks through like the query that you people, a lot of people would write, uh, that would get them the incorrect results. Right. Like this, because this does not account for nulls. This just returns us one row, uh, for the five that we put in both of these. This messes up anything where there are nulls and, and, and that doesn’t get us what we want back.

Right. This is obviously an incorrect result. Uh, you could rewrite the query. And this is how I see a lot of people do it where they have this gigantic or clause. A lot of ends sprinkled in a lot of people don’t get their parentheses, right?

Or don’t get there, or maybe they like copy, there are copy and paste errors in here. There’s a lot of things that can go wrong when you start writing, uh, non-trivial complicated queries like this. Uh, so this will get you the correct results.

Um, what is a little annoying here is this filter operator. Which expands out that entire or, the, the, all of, all of those or clauses for a couple of five row tables. Obviously this doesn’t make a performance difference, but when you’re dealing with larger data or darling data, which is the largest data you can get, uh, then these things do start to make a big difference.

This does get us correct results, right? This does bring us back everything that we care about. Uh, this will also get you correct results.

But as I’ve said in 10 million videos at this point, wrapping where and join clause columns in, uh, in functions, even built-in ones is just a recipe for performance disaster. Uh, so we can run this. We can get correct results, uh, but we would end up with, um, uh, just a weird sort of bunch of stuff happening, uh, in here.

There we go. So, uh, we have this whole predicate against this table, which I, which I suppose is a step up from a filter operator. But this whole predicate is just a series of case expressions for all the coalesces that we had in there.

So, uh, you can see the various case when, blah, blah, blah. Uh, case expressions are another thing that I, I, I strongly advise against putting into your join and where clause column, uh, predicates because you will, you will likewise be in for a bad time. Uh, there is also an example with isnel, and isnel should look roughly the same, except rather than a series of case expressions, we will have a predicate that is a series of isnels.

This is not necessarily a better situation. They’re both about equivalent performance wise once data becomes of a certain size. So be careful with that.

Uh, this is a much better way of writing the query because intersect will correctly deal with the nulls and it’s a whole lot less typing and a whole lot less error prone. So if we run this, we get back the correct results and we don’t have any weird stuff.

Uh, in our query plan as far as like gross predicates go, uh, all of this stuff just gets evaluated, uh, in a nested, in the nested loops join and we evaluate everything across there.

So it all ends up being fairly easy and straightforward. Um, there is sort of an unfortunate thing where not exist does not, uh, provide us with, uh, the entirely correct results on this.

We get back, um, an additional role that we don’t care for there. Uh, so, you know, uh, this is the best version of the query that I think could possibly happen. Intersect and accept are very useful for these sort of like, you know, uh, extended column comparison things.

So, uh, like I said before, um, I’ve never, uh, seen anyone really use these like when they should have, uh, in production queries.

Um, I think probably part of the problem is that it’s somewhat unclear what they do, uh, when you’re reading through any sort of SQL guide, uh, or rather even when you’re, you’re just looking at like, um, when the linguistic possibilities of SQL, you’re going to look at like, you know, stuff like join and exists and not exists and where and group by and order by and all the other words that come up.

And you’re going to say, huh, those make sense. I can sort of figure out what they do there. Intersect and accept.

it’s not really clear what they do from how they’re named. Um, uh, there are some weird rules around operator precedence with these two things. uh, has, uh, very specific operator precedence rules that other things don’t.

Uh, intersect will give you a unique set of rows from both queries and except will only give you a unique set of rows from the first query. First is going to be an air quotes there.

Uh, because, uh, like, you know, we’re just, we’re thinking about the queries as written in order. So the first one that you do, then the except, then the other thing, but then you could put other accepts and intersects below that.

Really, we just, we care about like the first one with except. Um, but the, the set of rows that you get back from them is going to be uniqueified.

So, uh, these are operators that work, uh, somewhat better or reason, not somewhat better.

I would say, uh, somewhere, somewhere between somewhat and profoundly better. If you have primary keys or unique indexes defined on the columns that you’re looking at, or at least one of the columns that you’re comparing there.

Uh, but I think probably the best part about them is that they handle null comparisons, uh, without a lot of crazy syntax, like we saw above with the ands, ors, and the is null coalesce and blah, blah, blah, blah, blah.

Um, that what is tricky about them is of course, figuring out when you should use them and what order to write things in. Often this takes quite a bit of experimentation to get, to start to get a real handle on.

And if you don’t use them for a few days, you will probably lose that handle entirely and have to come back and start from scratch. That’s sort of like when I have to write any XML query, I’m like, Oh God.

And I have to go reference everything that I’ve ever done before to figure out what I need to do now. But, uh, just to give you a couple examples of what they do, uh, let’s have, uh, let’s just do this.

And let’s say we want to intersect, uh, everything with the score over two with everything with the score over three. Uh, and we, we, we get the results we want.

Down here, we get about 6,500 rows and the old, but the, all the results are going to be everything where there’s a score over four from this, from both of these queries, right?

So this one is looking for greater than two. This one is looking for greater than three. And where those two results set start to actually find matching rows is when we hit a score of four. So everything in here is going to have a score of four and up, right?

So pretty easy stuff there. That’s where these two results start to overlap at four. We have greater than two. We have greater than three. What’s after three, four. What’s a couple after two, four.

That’s where we start showing what’s going to come back. Um, and like I said, uh, the user ID column, which is nullable and has nulls and it does not give us any issues here. Um, so if you, if you are writing queries and you have, find yourself having to like, you know, that compare, uh, columns like this, uh, especially like across a number of columns, often intersect or accept would give you better performance and, uh, better sort of, uh, code clarity and everything else because they handle nulls without a lot of, uh, overly verbose, uh, you know, uh, querying or adding in functions that replace nulls with canary values.

Uh, so this is, uh, this is the same two queries this time just using except. And, uh, this is going to give us just results from the first query, right?

Just from this one, right? Because we want to see everything from here except what’s in here. So we don’t show anything at all from this query. We only show the stuff from this query. Now this is only going to show results again from the first query, big air quotes there.

Uh, you could also call it the left, the left most query or the outer query with a score of three, because that’s the only data that exists in it. That’s also in the second query or the inner queer, right?

Or that’s, that’s not also in this one. So this we’re saying greater than three, this is greater than four. So where these start to collide is just at score three, right?

We’re only showing the threes in here. Um, so it is a bit like using not exists and that the rows are only checked from the second, from the, from this query. They’re not, we don’t project anything out from this one.

Uh, and again, the nulls are handled quite well. Uh, SQL Server 2022, uh, wow.

They, they, they really modernized T SQL with this one. Um, they added the, uh, is distinct from and is not distinct from syntax to SQL Server 2022. And I suppose at this point, we could be happy that they showed up at all because is distinct from was introduced in 1999.

99. This thing was old enough to drink before Microsoft got around to adding this in. And, uh, actually this one, this, this one was too.

Okay. I guess this would have been the funnier joke. Uh, this one was added in 2003. Well, I guess in 2022, it wasn’t quite old enough to drink. I guess it depends on where you live. If you’re, if you’re in those devil may care European countries where teenagers just get sloshed in the streets.

Well, my, actually that sounds kind of good to me. Uh, that’s what I did.

And I, I just had to find sneaky ways to do it because I didn’t want to get busted by the federal allies. Uh, so I mean, I understand no, no database really adheres like perfectly to ANSI standards, but waiting like 20 years to get, uh, basic syntax added to T SQL is, man, it is the, the choices Microsoft makes wild when it comes to this stuff.

Uh, I realized that like, you know, people aren’t exactly clamoring for like some of these things. things, but just like basic stock functionality where like, if, if you wanted to, you know, bring an application from, uh, like Postgres or Oracle or DB2 and put it in SQL Server and like, like, you had queries with this stuff written in there and you were like, Oh, that’s broken.

How do we rewrite it? It’s like, great. Awesome. Right. Good stuff. Good stuff.

Good stuff. Microsoft. We really appreciate all the wonderful things you’ve added to the product in the meantime that have changed everyone’s lives. So anyway, they are, the, the, the, the is distinct from, it is not distinct from what will be useful to someone someday when they start.

I don’t know. At this point, the SQL Server 2025 is probably going to be where people go. Not a lot of people went to SQL Server 2022. Apparently everyone was pretty happy with 2019.

Uh, I don’t know. I don’t, I don’t know fully what 2025 is going to bring us. Uh, there was just the announcements that it, it ignited fairly recently, which were, uh, I, I suppose, whelming at best.

Uh, of course, there was a lot of talk about, uh, fabric and AI, which, um, are two stupid things that, uh, of course being stapled onto, into SQL Server or SQL servers being stapled into them.

I don’t know. Uh, but there, there wasn’t a lot of, uh, wasn’t, it wasn’t a very good highlight reel about what’s actually in SQL Server 2025. Aside from stupid current hype cycle memes that are being shoved down all of our throats with, uh, very little care.

Anyway, uh, let’s look at how you can use is, is not distinct from, uh, I don’t, I don’t know if I have an is distinct from example here, but, uh, let’s say that, you know, one of my least favorite things to see in, in, in a join is an or clause.

All right. Something like this. Yuck, grotesque, awful, you do this sort of thing. You should have your keyboard removed from your hands, placed elsewhere.

Uh, but let’s run these two queries. And do, do, do, do, do, do, Yep.

Okay. Uh, there we go. They finally finished. So, uh, this first query, uh, took 6.2 seconds. This is the bad query with the or clause in it.

Uh, and it has all of the hallmarks. I’m going to make this a little bit bigger so we can see both the queries on, both the query plans on one screen here. Uh, this, this, this has all the hallmarks of a join with an or clause.

Uh, uh, we start off scanning the, well, I mean, you’re not always going to see a scan. You, you could technically see a seek here if we had a where clause on the user’s table, but we take all 2.4, some odd million rows from the user’s table.

We feed them through the constant scan operator twice. Notice that we have two sets, two full sets of rows from the user’s table. We have, um, uh, one set to find, uh, u dot ID, uh, in here.

So that’s, that’s quite a bit of not fun. And then down in here, we seek into, we, well, we do a bunch of work, right? We try to collapse all this stuff into here and we can catenate them and we sort them and we merge interval them to remove duplicates.

And then we spend time in here doing a nested loops join to the post table over and over again. And then finally 6.2 seconds later, we come up with a result set.

Hooray, hooray, hooray. We did it. Uh, this query down here, now granted, you know, there, there might be some indexing stuff we could do that would make this a little bit happier, but, uh, this takes about two and a half seconds, a pretty good improvement from six seconds, right?

Maybe, maybe not where I would stop if I were, you know, if I were being paid to tune this query, but I would, I would at least say, Hey, we could just do a little rewrite and get this in a better place. So that’s, that’s cool there. So I am excited for, to be able to start using these.

Uh, there’s just not a lot, a lot of opportunity for that yet. Um, you know, uh, I, I, I think that, um, T SQL additions like this should get back to, backported because like, they’re, they’re important, but, um, what do I know about backporting?

I’m just a, just a guy who writes door procedures. Uh, you could, there’s also, you could also read, it’s in the spirit of intersect and accept. We could also rewrite the queries in this way.

Now, uh, uh, that what’s, what’s fun about when I say fun, again, lots of air quotes in this, this, this video.

Uh, what’s, what’s interesting about query tuning these days is, um, the amount of things that may or may not kick in depending on how SQL Server feels at the time. Uh, things like batch mode on rowstore are, unless you force the issue with like, you know, some sort of, uh, column story thing, uh, it is entirely based on heuristics.

And so you can end up with like just strange performance differences, like for two queries that are, do they do sort of equivalent things like the join with the or clauses just get out. But if we were to run these two queries we’re using is not distinct from here.

And we’re using sort of the point of the video intersect here. All right. And if we look at these two query plans, this one runs for 2.6 seconds, just like above.

And this one runs for six, 600 milliseconds, which is quite different from above, right? Like the intersect query went faster. The intersect query went faster because SQL Server naturally chose batch mode on rowstore for this, for this query plan.

We have the batch there. We have the batch here. We have the batch here. Uh, I don’t think anything down here is eligible for the batch, except maybe reading from some of these, but, uh, we go, so we got, we got the batch up here when we read from the post table as well.

So SQL Server was like, yeah, batch mode on rowstore. Sounds great. Did not choose that here. Uh, and like I said, we could, we could force the issue, um, running, uh, this query and doing a funny little left join to a empty table with a columnstore index on it.

And if we look at the execution plan, now that we’ve got some batch mode in here, this one is all of a sudden competitive with the, uh, the intersect version up there.

So like I said, query tuning these days, real fun. Cause who knows what tiny little change you can make that will awaken SQL Server’s senses and say, oh yes, we should use batch mode.

We’re doing stuff with a lot of rows here. That would be smart. Um, so, you know, uh, keep an eye out for these things. Uh, I don’t really know where else to go with that.

Um, it’s a good, it’s a good time. It’s a real good time. Uh, so, uh, that’s about it for intersect and accept.

Um, I’m going to wrap this one up and I’m going to apparently talk about views versus CTE, which will be a rollicking ride. Uh, we’ll do that, I guess, as soon as this uploads.

So, we, we, see you then. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. I hope that you will expand your SQL vocabulary and start using intersect and accept. And I hope that if you, as soon as you are able to, you’ll start using is distinct from, and is not distinct from, as they become linguistically available to you.

Whatever SQL Server version or edition you end up on next. Thank you for watching. there’s a bright at heart and começa the людocker option, I wonder if or not could help you know why. A pastidade or assistant speakers,

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.

How To Write SQL Server Queries Correctly: UNION and UNION ALL

---

Thanks for watching!

Video Summary

In this video, I delve into the nuances of using `UNION` and `UNION ALL` in SQL Server queries. Erik Darling from Darling Data shares insights on how these operations can affect query performance and result correctness. I highlight the differences between `UNION` and `UNION ALL`, explaining why `UNION` adds distinctness at the end, while `UNION ALL` simply concatenates results without deduplication. The video also explores various placement scenarios of these operators within a query to demonstrate their impact on execution plans and performance.

Furthermore, I discuss when it’s appropriate to use `UNION` versus `UNION ALL`, emphasizing that most of the time, `UNION ALL` is more efficient due to its lower overhead for deduplication. However, there are cases where ensuring a unique result set can optimize other parts of the query plan. The video includes practical examples and SQL Server 2022 function demonstrations to illustrate these concepts, making it easier for viewers to understand how to write queries that return accurate results efficiently.

Full Transcript

Erik Darling here with Darling Data. And we’re going to talk in today’s video, continuing on with our series about how to write queries correctly. And this, of course, you know, comes down to two things, like both getting an accurate, correct result, you know, according to the logical demands of the query, and also having it return data to you in as efficient a manner as possible. And today we are going to talk about Union and Union All, or as they say in the South, Union Y’all. All right. If you can forget that joke happened, and you would like to support this channel, not for the jokes, but for the SQL Server information, or if you like that joke, you can do it for the jokes, too. I don’t care.

Whatever your motivation is, is fine with me. There’s a link in the video description where you can become a member of the channel. If clicking that link is too hard for you, perhaps clicking other things you’ll find a little bit easier. Liking, commenting, subscribing, all wonderful things to do. If the topics in these videos are near and dear to your heart, and you’re having SQL Server issues that you think a young, handsome fellow like Erik Darling from Darling Data could help you solve in exchange for money, I am available for hire for any and all of these things. Have a great time with me together, one-on-one, solving all sorts of stuff.

Training. Good to have. Better to watch. I had someone email me and say, hey, when I go to your training site, I can’t add anything to the cart, and I can watch all the videos. Did I buy this before? And so I went and looked in my receipts drawer, and lo and behold, they had purchased the training in December of 2020. So I said, yes, you did purchase this.

Merely, I mean, just like on the cusp of four years ago. Training works best when you actually watch it. It’s a participation sport. You have to be involved. Otherwise, you still know nothing. No upcoming events. 2025, talk to me. Tell me where to go. I don’t know where you are or where you want me to go.

I’m not endowed with psychic abilities, though I wish I were. So you have to tell me where you would like Erik Darling to be. With that out of the way, let’s talk about union and union all. So union and union all are funny because they kind of get used interchangeably in almost the same way that a lot of other things are, with very little consideration for performance or result correctness or other things.

So like CTE, temp tables, temp tables, table variables, parameters, local variables, all sorts of things that, you know, joins and exists. People just, you know, start writing a query one way and then they just always write the query that way. I remember a long time ago I read it. I used to play drums when I was a kid.

And I was reading an interview with a drummer in some drummer magazine and he was like, Ah, yeah, you know, I was playing in this cover band and, you know, we were like, you know, it was good because it was a very successful cover band. But, you know, every time I sat down to play drums, the only thing I could think of to play were like the drums to these cover songs.

And I was like, like, wow, that’s depressing. And then I realized, wow, that’s how people write queries too. They sit down and they’re just like, Oh, I’m going to play Copacabana again, I guess. So there are lots of times when I see developers use union, like when the results have absolutely no chance of having duplicates in them.

They either like join different tables together or have different where clauses, or sometimes they even have like literal values in the select list. They’re just like, you know, like, you like, how can you how can you make those distinct? How can you do? How can you do that? It’s weird. You already have distinct in the select, how are you going to make it more distinct?

And, you know, a lot of this stuff comes from testing queries in isolation, not really knowing that, you know, what when you should use exist first joins, because they haven’t watched the other video in this series about exists and joins. And, you know, once you start getting other things involved, like no lock hints, you could just end up with crap everywhere.

Bad things popping around everywhere. Now, how to write queries correctly does depend on a number of things. A, the quality of your data in general.

B, the quality of the data structures that you have available. That largely means the indexes. And also, you know, like, what returns a result to you as quickly as possible that is still logically correct. This can all become really difficult to figure out.

You know, especially the more complex a schema is, the more things you have to get involved, the more calculations you have to do. Knowing what, I think, really the hardest part about writing a query, aside from knowing, like, the SQL behind it, is knowing what the correct result should look like. It’s a very hard thing to define up front.

You have to, like, write a query, get a result, and probably show it to someone who’s like, who can be like, yeah, I don’t know either. Probably. I guess it’s right.

Seems fine. You know, validating query result correctness is a challenging, challenging thing. I’m happy when my rewrites just match what the slower query returned.

I have no idea if that’s right or not. I’m not checking that. I’m just making sure that what, like, the same number of rows and the same data output is as far as I usually go. Because I don’t usually get involved enough to, like, dig deep into, like, someone’s data and understand what an actually correct query result would look like.

It was sort of a funny story where there was one client of mine who had a very, very slow recursive CTE, which I helped fix up. And the original version of it would just never return a result. It wasn’t like an infinite loop thing.

It was just slow as hell. And when we got it right and we sped it up, they were like, I think the logic in here is wrong because these results aren’t correct. So we had to, like, redo the recursive CTEbecause they were like, yeah, this isn’t showing us the correct thing.

So that was fun, too. So let’s use a rather nifty SQL Server 2022 function. So let’s start talking about union and union all and how they differ.

And actually, union and distinct is what we’re going to talk about first. So I’m going to, you know what, I think I actually already did this stuff. If we look at these two query plans, there should already be stuff in these tables.

Yes, there are. Okay, great. So we have two queries here. The first one says select I from T1, union select I from T2.

The second one says select distinct from T1, union all select distinct from T2. The thing that I want to show you here first is, of course, the results. We get very different results back.

The first one just returns 1, 2, 3, 4, 5, 6. The second one returns 1, 2, 3, 4, 5, 1, 2, 3, 4, 5, 6. So even though we had 1 through 5 in this table twice and 1 through 6 in this table twice, these queries made different things unique at different points.

If we look at the query plans for these, the union query adds its distinctness at the end across both result sets. So we not only deduplicate the results from each table scan, but we deduplicate the entire result here. For the two distinct queries, the distinctness happens here, not at the end.

Right? So when we say select distinct from this one, select distinct from this one with a union all in the middle, we make this distinct and this distinct, but we don’t make the final result distinct.

We just spit back whatever these two things put together. So distinct and union all do make things, distinct and union do make different things unique. Most of the time, if you are using union, rather all of the time, if you are using union between two queries, you don’t need to also add distinct to the select list because you’re already going to get that at the end.

You might just be doing weird extra work. Another thing that is kind of fun about, is about union and union all placement in a query. So if we look at, oh, I should have highlighted the whole thing, shouldn’t I?

If we look at this and we look at the query plan, we just get a constant scan. If we were to quote any of these things, then, you know, it wouldn’t make a difference. But if we start changing these, we will start seeing slightly different results.

For example, this one removes an extra one and two, or sorry, removes an extra one, but we still have two twos and two threes. I think the execution plans for these are just kind of funny because we have two constant scans and a distinct up here, but then just a concatenation for the other two things, which are down below this.

If we quote this one out and this one in and run this, the execution plan now has this with the distinct over here, right? We have a constant scan, constant scan, concatenation, constant scan, concatenation, and the results are one, two, three, three. So we have removed some additional, we have removed that extra two from the query results.

And of course, if we put the union down here at the very end, we will see a slightly different query plan and a fully deduplicated result set. One, two, three. We look at the query plan for this.

We have one constant scan, one constant scan, and then the duplication at the end, just like with the query where we selected from the temp tables. So that’s fun right there. Now, there has been quite a lot of performance talk about union and union all over the years.

Well, I do agree that most of the time, as long as you get correct results, union all is going to be a bit cheaper on you because SQL Server will not attempt to deduplicate the result sets. Even attempting to deduplicate unique result sets, if you have a bunch of string columns involved, can be rather painful and unwieldy.

If you are in the habit of writing union queries or even putting distinct in your select list, I would really strongly encourage you to think about the number of columns you’re selecting, the data types of the columns you’re selecting, and what actually identifies a unique row. Because you might be doing distinct over a bunch of columns where it’s not making a difference. You might be doing union over a bunch of columns where it doesn’t make a difference.

And oftentimes there is a sort of hidden subset of columns in your data that you can make an easy sort of distinct result set from without having to worry too much about it, without having to worry about long select lists and stuff. So if we run this and we look at the query plan that comes back, this takes about two seconds.

This is not a terribly dramatic example, I admit it. And, you know, it’s okay. But, you know, the thing is that we’re, you know, doing select all this stuff and including this text column and, you know, it’s just, it’s, it’s, this is an envarchar 700.

And now you have to worry about deduplicating that. If we were to take this query and do this, and what we would be doing is taking the same base query with the same columns in it, but then only generating a row number over the columns that we know make a unique result set, this will, this is a little bit faster, right?

Like I said, this is not a terribly dramatic example. That’s about 400 or so milliseconds faster, but it’s still a good example of how you can improve things by thinking a little bit more, a little bit more analytically about your data and what you need to make unique and what you don’t.

Right? So like in this case, doing a, generating a row number over these two union all the result sets is a lot faster. Now we’re now when I said most of the time, I do mean most of the time, there is less overhead to union all over union.

But there are some cases where it does make sense to make a unique result set to make some other operation in a query plan more efficient. Uh, I want you to think of this sort of like, uh, pushing predicates down to when you touch tables rather than having a filter operator happen later. That’s something that I talked about in the, uh, exists versus joins video, where I showed you a query that uses a left join, uh, with a, a where clause, uh, to find rows that don’t exist in one table that exist in another table.

And how using, uh, not exist was much more efficient in that case because we joined less data together, right? Because with the, the left join thing, we had to fully join both tables and then filter out nulls afterwards. The same thing is for like pushing any predicate or reducing rows as much as possible before you do something that is, uh, computationally expensive in a query.

So sometimes getting a distinct set of rows for your query can make things a lot better. So what I’m going to do now is, uh, populate this temp table with, um, user IDs for people who have won these badges. And the first way that I’m going to run this query is with a union.

And we’re going to marvel at the query plan for this. So this takes about, let’s see, five seconds right there. Right.

And, uh, you know, this, this would get better with, you know, slightly better memory grant stuff like that. Uh, if I, if I ran this like multiple times, you would see it get a little bit faster because SQL, since I’m in compat level 160 for the other stuff, we’re getting memory grant feedback. So this would improve over a few runs that we would eventually see that spill kind of fall off.

Usually it’s like three or four runs before the spill goes away. But, um, anyway, now what I want to do is change this to union all. So now we’re going to be taking like, and before with union, we were deduplicating these results, right?

We were getting rid of them. Um, but now when we do this, what we’re going to notice is that this query no longer finishes in like five seconds. This query drags on for a little bit longer.

And by a little bit longer, I mean, this thing is going to run for about 30 or so seconds total. Um, it’s been a while since I timed this one. So, you know, who knows, maybe, maybe, you know, Intel gave me some supercharged boost to my CPUs and maybe it’ll finish a little bit quicker.

Um, maybe not. I don’t know. We’re going to, we’re just going to, we’re just going to let this thing ride.

Uh, and we’re at, well, we’re at 35, 36, 38. Oh, we’re at 40 seconds now. Uh, do, do, do.

Well, I don’t know. I think this, this might be proving the point a little too well. So we got up to almost 50 seconds on this one. We got 47 seconds. And that’s because rather than, um, and, and, and you can see that like the, the, the, the, the pain of this wasn’t even in here.

Like there was almost no overhead to like doing the concatenation of these. Uh, we did still have to, you know, do all this stuff in the sort and whatnot, but where this makes the biggest difference is the number of rows that end up or the number of, uh, things that we end up doing in the table spool. Right.

So we have a nested, we have a nested loops join here. SQL Server uses a table spool here. Um, I’ve talked about table spools in the past, but they are sort of interesting. Uh, SQL Server uses them on, like for in select queries, not modification queries, modification queries, spools are for Halloween protection.

In select queries, spools are often there to optimize, uh, operations on the inner side of nested loops. By the inner side of nested loops, I mean this portion of the query plan in here. So, um, a table spool is fun because a table spool, uh, you know, you take, uh, sorted data from this table, right?

You sort it here and then you pass it to the nested loops and the nested loops, uh, will, you know, tell the table spool to go run the query for, let’s just say ID one. Uh, it’ll populate the table spool. Uh, it’ll populate the table spool.

And then for any additional runs with ID one, it’ll reuse the data in the table spool. Then as soon as we get to ID two, the table spool will get truncated and repopulated. And then, uh, you’ll like, it will, it will reuse data in the table spool for ID two until we hit ID three.

So table spools really can save a lot of time and energy on the inner side of nested loops sometimes. But in this case, the problem is more that we end up with way, way, way more stuff to do with the, because we don’t deduplicate results here the way that we did with the union query. So if we go back and we quote out the all here, remember this is about 40 something seconds and we run this.

We’ll do this one more time with the, with the, the union rather than the union all, it should be about five seconds or so. So do do do. And we get the results. Notice that, uh, like, you know, we, we, we do the same thing where we, you know, concatenate the results. But then before we, uh, send it, send it along, we get, we use a distinct sort to make the results of these two things distinct.

And we spend a whole lot less time in this portion of the query plan. Right? So this, like this part does a whole lot less work because we made the results set unique in this part. So you can run into interesting situations where using union to deduplicate results makes a computationally expensive part of a query faster or repetitive part of a query faster.

In this case, the nested loops is really what did it. So that’s a, that’s a pretty good way of thinking about things sometimes where can I make, can I make this part of the query execute fewer times or do less work with a distinct result set rather than a non distinct result set. So anyway, that’s about all I have to say about union versus union all. As always, I hope you enjoyed yourselves. I hope you learned something.

I hope that you will continue watching this series. I hope that you will continue to write queries correctly. And I will see you over in the next video, which is going to be about two things that I never see anyone use. Intersect and accept.

Fun times. Oh boy. You’re going to really, you’re going to get a relational mouthful on the next one. So I will see you there. Goodbye. Bye.

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.

How To Write SQL Server Queries Correctly: Views vs Inline Table Valued Functions

---

Thanks for watching!

Video Summary

In this video, I dive into the world of views and inline table-valued functions in SQL Server, comparing their pros and cons while highlighting common pitfalls that can lead to performance issues. Erik Darling from Darling Data shares his experiences working with clients who have created overly complex view hierarchies, emphasizing that views themselves are not inherently bad but can become problematic when misused. I also explore the benefits of inline table-valued functions, particularly their ability to accept parameters and push predicates further into queries. Additionally, I discuss Microsoft’s recent fixes for certain issues related to parameter sniffing in views, explaining how to enable these fixes on different SQL Server versions. By sharing practical examples and insights, this video aims to help database administrators and developers make informed decisions about when and how to use views versus inline functions in their projects.

Full Transcript

Erik Darling here with Darling Data. Having a lovely day so far. Absolutely lovely day. Stunning day. It’s freezing cold outside. In today’s video, we’re going to go to the next topic in our How to Write Queries Correctly series. In this one, we’re going to talk about views and how they compare with inline table-valued functions. I know that it just says views there with an exclamation point. I kind of ran out of room, but then it looked funny and the spacing was weird. So this is what you get. But there are some differences and there are some things that we should talk about because I see a lot of people making the same mistakes over and over and over again. And quite frankly, I’m getting tired of fixing them. So I’m hoping that we can talk through some things today and you can start not doing the wrong thing. First time for everything, right? As usual, if you like the channel and you want to support the channel, there’s a link down in the video description below. If you don’t want to do that with these things, you can do these things up there. If you need consulting for SQL Server, if you’re like, wow, that Erik Darling, he knows what he’s talking about. He seems like a nice, reasonable, fellow with reasonable rates who I could work with on MySQL Server performance problems. You can hire me to do all of this stuff. It’s a pretty good deal. If you’re into good deals, how about getting all of my training for about $150 for the rest of your life? Hard to pass that up. No upcoming events, end of year, blah, blah, blah. With that out of the way, let’s talk about views and functions and stuff.

Now, there are a lot of bad things that one could say about views because over the years, we, probably we, the royal we, I really just mean me, is a performance tuner, have seen people just do absolutely awful, egregiously disgusting things inside of their views. Views on their own are not the problem. It’s the way that people treat views. Views on their own are not the way that people treat views. They end up sort of being like a junk drawer for just like weird query logic.

And, you know, this is like coming from like a rather like personal place right now because like current, like one of my current clients, I was, I was trying to figure something out. And I did SP help text on eight view names before I found a view definition that touched one single physical table in this database. Every other view definition was selecting stuff from one or more other views.

I still haven’t gotten to like the root view, the like original vampire view that has everything in it. Like I just got to a certain point and I was like, that’s an, I, we’re not going any further. I have this figured out enough, but like eventually at some point I’m going to, I’m going to whittle it down and I’m going to figure out what the like core criteria is that is throughout these 50 billion views. And this is a common mistake that people make.

They think that views are some sort of performance thing. They think that views are automatically a materialized result set. They don’t understand that all they’re, all they’re doing is interacting with a query that has a name, right?

They’re just housing this query. And I understand the point of, of, of making them because, you know, if you have, you know, a lot of tables and, you know, it’s hard to remember all those joins correctly. And, you know, making sure you have like the one or more join columns, right?

And, you know, whatever other, you know, you know, manipulations you have to do to data in order to get the results that you’re after. They can, it can be time consuming and annoying. So I, I understand why people make views, but the things that they end up doing after that are just, just sinful.

Now, views really, you know, if we’re going to make, make a statement about views, they’re, they’re only as bad as you make them. If they’re bad, it’s your fault. You, you did all the bad stuff.

You put all the bad stuff in there. You, it was you. The view did not force you to do it. The view did not change itself overnight to become an evil view. You put all the bad stuff in the view and, and, and now you’re living with it.

So you have, you have reaped what you have sown, right? Reaped what you have sown. Whirlwind is in stuff.

So, let’s talk a little bit about the case for views, because there, there, there is one nice thing about views. And that, that is that if, if you obey like the 10 million rules that Microsoft has put in place, you, you can index a view. And then you do have a materialized result set and that is pretty nice.

Um, it, you know, uh, it gets a little dicey if you have more than one table reference in the view. Like if you’re joining multiple tables together, things can get kind of awkward with, uh, index view maintenance. Um, but in general, and like, you know, a single table index view, uh, with, uh, indexes, uh, available on the table in order to make index view maintenance, uh, very quick and efficient are really no different than having another nonclustered index available for your query.

Uh, there are some kind of funny things about index views. Like, um, you have to use the no expand hint if you want, uh, column level statistics generated on the view. Um, if you’re on standard edition, you don’t get the index view matching to the same way that you get it with enterprise edition.

So the no expand hint becomes even more useful, but, uh, even on enterprise edition, you need no expand for the column level statistics thing. Um, you know, so there’s, there’s stuff about index views that is kind of tricky. I’m not going to put them on the same level as partitioning because index views actually can make queries faster.

Whereas partitioning just doesn’t make queries faster. You’re lucky. You’re very lucky if you partition a table and, and, and, and performance stays the same.

Uh, in reality, partitioning a table is absolutely no different from having a good seekable index. On the table. I think where a lot of people kind of get confused is that when they partition the table, they changed the, they changed the definition of the clustered index and made it match better.

The, the sort of, uh, the path that the, the queries were taking to the data they wanted. And they’re like, wow, partitioning was magical, but really they just indexed the table poorly to begin with. So, uh, there are, so I forget the application name.

Well, like I’ve run into it with Looker. I know that there are a couple others that, um, sort of build queries based off metadata. And, but they, they, they’re unable to do that with inline table valued functions.

Um, they really only, uh, do that with views and stuff. For some reason they can’t see, uh, what that is to build queries off of it. Um, so, you know, so there’s that, uh, I, I really dislike having crappy applications like Looker dictate what sort of database objects I can use and create.

Um, but you know, some people make bad choices outside of the database too. And, you know, you’re, you kind of get stuck with them. Uh, index views, you know, they’d be, they’d be great if Microsoft would invest like an ounce of time into them.

Uh, they really haven’t gotten anything aside from like, you know, uh, like bug fixes, uh, since they first came on the scene. Um, that, uh, it’s, it’s really, it’s really a bit disgraceful what other database engines, uh, are capable of doing with index views and allow an index views that Microsoft does not. A bare minimum, like min and max aggregates.

Like how, like, like really, you can’t do that in an index view? Like what, that’s a sad, it’s a really pathetic state of affairs, really. Uh, you know, we, we, we’ve, we’ve gotten so many awful features that have, that have died on the vine.

Uh, and, and we can’t get min and max support and index views. It’s, one, one wonders where the people in charge of SQL Server store their heads. We, we wonder if maybe there’s a, there’s a glass belly button joke hiding from us in there.

Um, one kind of weird thing about views, and this is something that, uh, I, I, I, I, like I have read before, but it never really sticks with me. But you can create views with a, with, with an, with check option. And, uh, I’m just gonna read from the documentation that it forces all data modification statements executed against the view to follow the criteria set within select, within the select statement.

I don’t know why there’s an underscore there. I copied and pasted this. Uh, when a row is modified through a view, the width check option makes sure that data remains visible through the view after the modification is committed. Okay. If that’s important to you, use the width check option.

Um, I, I don’t know when I’d want that to happen. Uh, can’t think of anything quickly. So, views, you know, uh, they, there’s a, there’s a tremendous propensity for people to put a lot of bad crap into views and ruin performance over time by nesting, nesting, nesting, nesting, nesting, nesting, and putting worse and worse, more complicated queries in each sort of level. Uh, and then, you know, uh, at some point along the way, you’re like, oh, hey, hey, Dante.

Oh, Lucifer, it’s you. Nice to see you here. Uh, but inline table valued functions, you know, they, they, they do offer some things that views don’t. Uh, namely the ability to, uh, add parameters to them, right? Views don’t accept parameters in the, in the definition inline table valued functions can.

And that can allow you to push predicates a little bit further into queries, uh, in some circumstances than views will allow. Well, we’re going to talk, we’re going to show you that example in a minute. Um, you, of course you cannot index a function.

There are no, no such thing in the current state of SQL Server is, is, is materialized functions. Uh, so, I don’t, I don’t really know that I care. I mean, it’s not, it’s not, it’s not, it’s not that big of a deal.

And I can only imagine what awful things people would do with them. Uh, but, uh, the, the ability to pass parameters to a function is a pretty big deal in some cases. And again, we’re going to look at that.

Now, um, it would be nice if, uh, Uh, there were a way to pass parameters as queries to certain things, right? Like it would be cool if, you know, like if you had like, just, you know, as a, as a sort of a stupid example, let’s say you had a store procedure that took, that was responsible for taking full backups.

And, uh, you know, like the, really the only way to pass a parameter to that or pass a value to that, uh, store procedure and then have it do something with it is to exit is like, you know, build a loop or a cursor or an array, like a CSV and pass it into the store procedure. But even then, like, even after you, if you, even if you pass a CSV, uh, like a variable or parameter in, uh, you still have to break it apart and do stuff with each individual line inside the store procedure. It’d be really cool if you could do something like this, where, you know, you would say, take a full backup and database name is the result of this select query.

Like that would be awesome. Cause then you wouldn’t have to do all the like weird internal work to like, you know, uh, write a cursor correctly or write a loop correctly. And then like, you know, other things you could, you could just do this and life would be a lot easier.

Uh, but you can’t, which is kind of lame, but you know, it’s not, not really the whole point of this. Now, the thing that I want to show you with inline functions is, um, uh, they, when I first came across this, I was really, really puzzled, uh, because there was a view and, uh, uh, in, in, in, when you called the view, like in, like in, like outside of a store procedure with a literal value, everything went fine.

When you called the view inside of a procedure with a parameter things went not, not fine. And we’re, we’re, we’ll, we’ll talk about that. But, um, the, the thing that, so like that, that does lead me to Microsoft did add a fix for this.

Uh, the thing is you have to be on, uh, at least SQL Server 2017 CU 30 and have query optimized or hot fixes enabled, uh, in order for it to kick in. Uh, I’m not exactly sure which CU for 2019 this was available on. Um, I would probably, I would, I would imagine that I can’t remember if it was available in 2019 from the get go or, but I’m pretty sure it was back ported because it’s under, it’s, it’s, it’s, it’s, it’s, it’s, it’s, it’s, it’s in 2022 under compat level 160.

So I think whatever CU came out around the same time as CU 30 for 2017 is probably where this thing ended up for 2019. Um, but, uh, if you’re on SQL Server 2022, you can just use compat level 160 and get the, uh, get the same behavior. So, uh, what I want to show you here is that you could run this and get the fix that I want to show you, but I have query optimizer hot fixes not enabled.

Uh, you could also enable trace flag 4199 as long as you’re not, um, you know, Oh, geez, that could have been a disaster. As long as you’re not, um, uh, you know, uh, hampered by anything like, uh, being on, uh, Azure SQL DB or managed instance or, uh, not have sysadmin privileges. It’s kind of a downside of DBC, of D trace flag stuff like, uh, like DBCC trace on or, uh, uh, you know, query trace on and there’s a query hint.

Uh, and I’m also not in compat level 160, even though I am on SQL Server 2022. Currently I have this thing set to, I think, compat level 140. Um, just because, uh, I don’t know, sometimes too many things kick in, in the higher compat levels that makes coming up with, uh, good repeatable demos kind of difficult.

And, uh, as a presenter, I often just need good repeatable demos. Um, sometimes the higher compat levels do offer that because they do something really bad with some of these new features. But, you know, for the most part, I like just the stability of like not every single intelligent query process or feature trying to kick in and, and like, you know, ruin my day.

But anyway, uh, we have this view and the main, the main thing in this view that will cause us, uh, problems down the line is going to be the windowing function. Now, uh, I know that I talked in the CTE video, the first CTE video about, um, there’s going to be a second one. So that’s why I’m saying the first one about how, about pushing predicates, um, to window functions, uh, from outside of CTE.

And how, if you have the, uh, the partition by column, uh, is the column that you’re filtering on SQL Server has an easier time of doing that. But, uh, that does not hold true with views in all circumstances. Let me just make sure I actually created that.

So, uh, we have query plans turned on and if we run this query, this will run very quickly. Uh, we will have a very nice, easy index seek right here. Everything is fine.

Uh, even though, uh, well, simple parameterization was at least attempted here. Uh, I don’t, I’m not going to dig in and figure, I don’t, don’t believe it was successful because we, this, the query plan looks like this. But, um, the reason this works fine is because we have a literal value right here.

Uh, not a variable, not a parameter, not a placeholder of any variety. So everything kind of looks how we would expect because we have an index on owner user ID. And, you know, uh, we’re also, uh, that also has the score column sort of descending in it.

So our making this dense rank is very, very simple for us. Now, uh, I could do all, any of the things that I mentioned above with the, the database scope configuration with the trace flag, blah, blah, blah.

But, uh, what I want to show you before I do any of that stuff is what the query plan looks like with a parameter touching that view without any of the fixes in place. And this takes a little bit longer and we no longer have a nice, simple index seek plan. Uh, now this takes about seven seconds or sorry, about six seconds, I guess.

So we have a nice, simple index seek, we scan the entire table. Uh, we generate our row number over here, segment, segment, sequence project. And then way over here, we have a filter, uh, say where the predicate equals owner user ID, right?

The parameter that we passed in, uh, the, the limitation here is where SQL Server can’t push a parameter or a variable past the sequence project operator. It gets stuck outside of this thing. If we recreate the store procedure with option use hint, uh, query optimizer, compat level 160, uh, the plan will go back to what we expected.

It’s a nice, fast, simple index seek, and no longer having to wait about six seconds to do all this stuff, right? The, the, there’s no longer a filter over here. We were able to push that down past the sequence project.

It would be nice if a lot of other things, um, worked under, uh, worked when you hinted higher compatibility levels. Um, just as sort of a stupid example, like, um, uh, there are a lot of, uh, new, uh, functions and functionality added to T SQL, uh, with each release. And, um, you would think that if, if you put a query level hint on to say, like, use string split or string ag or one of those other things that comes along, but is only available under higher compat levels, you would think that you would be able to access that stuff just by using the query hint for a higher compat level.

But you, you can’t, uh, Microsoft wants you to use the, the, the, the higher compat level all around in order to get that, which, um, is, is a perilous venture. Uh, but the, since we’re comparing views and inline functions here, uh, I do want to show you that if we created the, this as a, as an inline table valued function. Uh, now, of course, this is not a scalar UDF and this is not a multi-statement table valued function.

Uh, we are returning a table, which just returns a select. There is no table variable. There is no data type involved here.

We are returning the results of a select query, right? So if we do that, we can pass a parameter in here and here. And if we do this, even without query optimizer hot fixes enabled, uh, SQL Server is able to, uh, push that seek or push that parameter down into there just the way that we would expect.

So even if you’re not having this specific problem, uh, I think it is often, uh, worth exploring, converting views into inline table valued functions. Uh, just because if there is a common filtering or joining criteria, uh, it’s very, very convenient having parameters to express, uh, express that into be able to pass those in. Um, it better shows the intent of the module and what it can be used for.

And it prevents developers from forgetting filtering. I thought that filtering criteria and getting really like just exploded out results. So, uh, this is just a sort of short walk through the differences between views and inline table valued functions.

Um, uh, you know, again, uh, views, you can materialize them. Um, if you follow the 10 billion rules, uh, inline table valued functions, you can’t materialize, but you can pass parameters too, which can be a very, very valuable performance tuning thing. Um, you know, when you like apply to an inline table valued function, then you can pass column names in, uh, for the parameters that you can get often get very, very nice, uh, performance increases doing stuff like that.

Uh, but anyway, uh, I hope you enjoyed yourselves. I hope you learned something. Uh, the next video will be about, uh, union verse union all.

Um, uh, we’re going to explore, uh, uh, sort of like where union starts making results distinct and things like that. And, uh, we’re also going to challenge, uh, uh, uh, a very common performance tuning, um, uh, uh, uh, I don’t know, just, it’s a strongly held religious belief that union all is always faster than union. And we’re going to look at an example where that is not true.

So I hope that, hope that you hope you’re wearing your helmet for that one. You’ve got your crucifix and garlic and all that stuff. So anyway, uh, I’m going to get this one, going to get this one sent along to YouTube and then, then record that one.

So see you shortly.

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.

How To Write SQL Server Queries Correctly: Common Table Expressions

---

Thanks for watching!

Video Summary

In this video, I delve into why Common Table Expressions (CTEs) are often overused and misapplied in SQL Server queries. I argue that CTEs can be a bit like 70s fashion—faddish and largely unnecessary, with little real benefit beyond readability, which is subjective at best. I explore the performance pitfalls of CTEs, explaining why they can lead to suboptimal query plans and poor execution times. Additionally, I highlight how different database systems handle CTEs differently, using Postgres as an example where CTEs are materialized by default, contrasting it with SQL Server’s behavior. The video also covers specific scenarios where CTEs might be necessary or useful, such as when dealing with windowing functions and row numbering, but emphasizes that in many cases, simpler alternatives exist. I provide practical examples to illustrate these points, showing how the placement of filters can significantly impact query performance and results. Finally, I touch on the future of T-SQL, speculating on potential improvements like the `QUALIFY` keyword, which could make CTE usage more efficient.

Full Transcript

Erik Darling here with Darling Data. Today’s video where we continue our series on how to write SQL Server queries correctly, I’m going to continue to aggravate certain portions of the public by talking about how completely stupid CTE are. Looking forward to it. Before we do that, let’s talk about stuff that we talk about all the time. If you like this channel, if you find the content in this channel valuable, and you would like to support this channel to the tune of four bucks a month or more, depending on your level of kindness and generosity, depending on where you are in your life as a matter of salary, you can click on the link right in the video description at the top there that says become a member, and you can join the 30 or so other people who have done that and contribute to the video description. I want to contribute a little bit to keeping the lights on for this channel. The light bulbs that it takes to do all this are very expensive. If four bucks a month is just too rich for your blood, for whatever reason, you know, your mom’s in a nursing home or something, liking, commenting, subscribing, all wonderful ways to make me feel cherished by you.

If you watch these videos and you think, wow, that Erik Darling sure does know a thing or two about SQL Server, perhaps he could make my SQL Server faster in exchange for money. You can hire me as a consultant to do just that. I am good at all these things, best in the world, according to most. So why not take a chance on that? If you would like some very high quality, very low cost SQL Server training, you can get all of mine, again, link in the video description down yonder. You can get all of mine for 150 bucks. And that’s for the rest of your life. No subscription required. Isn’t that lovely?

No upcoming events. No upcoming events. 2024. End of it. 2025. We’ll get into it again. I will go to as many pre-cons as my wife will let me. She does miss me terribly when I’m gone. So I can’t just fly around the country every weekend, but, you know, I’ll do my best to get to your very important event. But, yeah, let’s talk about, let’s expose CTE for the fraud that they are.

So CTE for me are a lot like clothes in the 70s. A bunch of people with absolutely no taste convinced a bunch of other people with absolutely no clue that they should dress just like them. And so if it weren’t for us getting the 80s after the 70s, the human race would have absolutely no redemption arc. In fact, since like probably the mid-90s or so, the redemption arc is descending. I’m not sure if you feel the same way, but boy, I think 1980s was the peak of human civilization.

That’s when all of the best things, all of the best music and movies and everything was going, fashion was wonderful. Nothing better. Now CTE were implemented to fill in some blanks that derive tables left, right?

Things like being able to re-reference them in queries and things like that. But the first, the problem with CTE is that the very things that they were designed or that they were implemented to address with derived tables are the things that make them suck from like performance-wise. I didn’t know what a petard was until I read this, but apparently it’s some kind of stick that you can hoist people with.

I guess it’s like a wedgie stick where you can pick someone up by the back of their underwear and just hoist them up and wiggle them around and make it real uncomfortable. But people use them the same way that they use nose and air hair trimmers where they just jam them in and wiggle them around. Obviously, nose and air hair trimmers weren’t my first choice of metaphor here, but I’ve got to keep it family friendly.

But they just jam it in and they wiggle it around. Maybe they just keep wiggling until they stop hearing hairs get trimmed here and then that’s it. There’s very little actual mental feedback for most people about if CTE are good, bad, or ugly.

You can’t see in your own ear too well and you can’t see up your own nose too well, but all you’re left with is a lack of clipping noises. It’s amazing to me how many people will just stick with, like get awful performance using CTE, but stick with them just because of this misguided notion that the query is more readable. They read a style guide.

The query is more readable. They read their 70s style guide and now that they’re showing up with crocheted bell bottoms and velour neckerchiefs and spread collar shirts with gold necklaces tangled in chest hair. Stop.

Okay, just do yourself a favor. Stop. Way back. CTE are one of the least advanced components in T-SQL. I’m going to cover more about that.

You can probably see up at the top there’s another tab 14, tab with numbered 14, where we’re going to talk a little bit more about CTE usage. But really I just want you to know there’s nothing all that interesting or advanced about them. Anyone who says they have anything advanced or interesting to teach you about CTE is either a complete simpleton or a charlatan.

Pretending that there’s an advanced notion, advanced usage of CTE. It’s the same level of idiocy as explaining joins with Venn diagrams. Like semi-colored circles is going to help anyone understand what their join query is doing.

It does absolutely nothing for anyone. So you should cast those people by the wayside because they are not good people. Now, if you’re coming from a different database platform, you might have a different experience with CTE.

For example, Postgres, by default and when considered safe to do so, CTE get materialized. They get like, it’s almost like a temp table. They cache a common sub-expression type thing.

It’s almost like a spool or a temp table, whatever you want to call it. It’s a temporary object that caches the result of the query. I have a couple examples from the Postgres documentation.

But if you’re coming from, like if you have a Postgres background and you’re used to CTE behaving in this way, you get to SQL Server and you’re like, wait a minute, why does this suck? Well, because it doesn’t behave the same way.

You’re also probably going to be wondering why your read queries are blocking and deadlocking with your write queries because SQL Server does not use an optimistic isolation level by default either. So woe to you, fine folks out there.

So with Postgres, you can choose. I mean, notice the red squiggles under here, right? Make this not valid T-SQL, maybe someday.

But in Postgres, you can choose to materialize a CTE result, which is just like sticking in an attempt table. And you can work off that.

But when you don’t materialize it, you re-execute the query in here as many times as you touch the CTE. Now, CTE, for some reason that I cannot fathom, get a lot of developer defense to the same extent that table variables get. It’s befuddling to me.

Like people will write a query, use a CTE, have it perform awfully, but then sit there and be like, oh, but it’s so readable. Oh, look how well I can read this query.

I have so much time to admire this readable query and re-read my query. Will I wait for this query to finish running because I’m sticking with this stupid CTE? It’s fascinating to watch.

It is, there’s some sort of mental disorder going on in people who do this sort of thing. Now, there are times when CTE will have no impact on anything, right? So if I run both of these, make sure query plans are turned on.

If I run both of these queries, one with a filter inside of the CTE, one with a filter outside of the CTE, we will get identical results and identical query plans.

And they make no difference in this case. None whatsoever. Right? So there are times when SQL Server just is nice enough to optimize the CTE away. It just throws it right out.

Now, one place where you do have to use CTE currently in SQL Server is if you need to sort of have some runtime expression, like a row number, and you want to filter on that row number.

SQL Server doesn’t offer a way to do this with a single query. Other database engines have a qualify keyword, which I’ll show you in a second, that allows you to do that without nesting your query at all.

So, but another interesting thing is that if you are going to put, you are going to use windowing functions in CTE, and you want to filter on stuff outside of that, sometimes SQL Server is unable to push your predicate up into where it, up into the CTE.

Now, it can’t do that here because the column that I’m filtering on, vote type ID 8, is not a partitioning element in the windowing function up here. So, what happens is I have to run this whole query.

I have to generate a row number over the votes table. And then once I get outside of the votes table, and I have generated my row number, and I start filtering on my row number, only then is it safe for SQL Server to apply a filter to the, to the, apply the vote type ID filter to the query.

Now, this, this sounds funny and weird, but obviously if I were also partitioning by vote type ID, I would be answering a somewhat different question with the windowing function.

Right? That would be like, like partitioning by user ID and vote type ID would mean that I am effectively asking SQL Server to rank things differently than just by user ID. There, there are times when it’s safe to do that in the partition by, but not here.

So, if we look at the query plan for this, you’ll see it ran for a heck of a long time. And if we look at what happened, the, the details of the filter operator, you can see that there’s a predicate on both vote type ID 8 and this expression 1 0 0 1 equals 0.

That’s basically this where clause right here. So, uh, obviously vote type ID 8. You can, that’s pretty easy to work out.

But then, uh, this being equal to 0 is the expression that is also evaluated in that filter. So SQL Server had to do all the work to generate the row number to then apply those filters later.

Uh, but I want to talk about how that is sort of answering a different question. So what I’m going to do is I’m going to create a table, uh, called the top answers of all time.

And I’m going to put, uh, uh, I don’t know, like 2,500 or so rows of Paul White in there with this number. And I’m going to put 10 rows of Erik Darling in there with that number minus whatever row number we’re at, right?

So row numbers 1 through 10, I’m going to subtract numbers 1 through 10 from this, right? So insert those rows in. And this is what the table looks like.

I have my 10 rows down here where I have a decrementing value from Paul’s big score down through all this stuff. And then I have, uh, 2,500 or so rows of Paul White’s high score, right?

So the reason why that answers, why the part, this answers different questions for like the example query that I was showing you up there was, let’s say that this is the initial query where I want to find the top answers and then find out the top 10 answers, right?

That’s this filter. And then ask if any of them have the answer, answerer name Erik Darling, right? So let’s just run this internal query first so I can show you what’s going on in here.

Obviously all of these 999s, they, they all tie, right? But they get, but they get an incrementing row number. It’s not like rank where rank would give you one for all of them. Uh, row number and dense rank will give you this incrementing number across all of them.

Uh, but if we get down here to the end, you’ll see my final 10 rows where like, obviously I’m, I’m out of the picture. So now if we run this to say where row number is less than or equal to 10, we’re going to have Paul’s 10 rows.

So if I’m asking the question, who are the top 10 answerers of all time? And are any of their names Erik Darling? The answer is going to be no, right? But if I change that and I put this in here, you’re going to, and I run this query, you’re going to see just my answers ranked, right?

So high answer to low answer. Now, if I run this query, we’re going to get those same 10 rows back because I only have 10 in the table, but you can, you know, if you want to just see a slightly different take on it, here’s the, here’s like the top five answers of Erik Darling.

So you actually answer different questions depending on where you put filters in for windowing functions, right? So that’s something to be aware of when you’re, when you’re using them.

Now, this does bring us to a case where CTE are generally okay because they’re generally needed, right? You can’t calculate in SQL Server at current, the way T-SQL is designed, you can’t filter on a row number within one query.

Like I said, you have to nest the query, generate the row number, and then filter on it. Now, Snowflake, a different database platform, has this keyword qualify.

Qualify allows you to either put a row number directly into the qualify clause like this, right? Like you can say qualify this equals one, or you can put a row number in your select list like this and then say qualify row num equals one.

Now, this is obviously just a little bit of syntactical sugar because you’re going to end up with the, kind of doing the same thing is like the query plan that we saw when I showed you the filtering thing where you’re going to have to run the query, generate the row number, and filter on it.

This is just a nice compact way of doing it. I think this should, obviously, I think this should be in T-SQL because it gets you out of a lot of like extra typing and nesting queries when you generate a row number. It’d be really nice to be able to do it in place like this.

Whether it’ll ever happen or not, I don’t know. Microsoft is busy burning all its money on OpenAI, co-pilot, so who knows, right? T-SQL just, who knows?

SQL Server might get no attention whatsoever. Who knows? Who knows, right? It’s crazy. You’ll probably get the ability to put a CTE inside of a CTE before you see any actual useful progress to T-SQL as a language.

But one of my favorite uses of CTE is paging queries. And this is a technique that, again, I mentioned Paul White for the five billionth time.

This is a technique that I learned in 2009 from Paul White blog posts where you can stack CTE like this. And this doesn’t have the same performance impact that re-referencing CTE via like joins does.

And I’ll show you what I mean. So let’s run this whole thing. And the reason why this is okay is because we only end up with, well, for the CTE part of it, I do want to point out that I do join back to the post table here to get, why are you red?

You should always be pink. You silly, you silly ghoul. We do have two references to the post table because I joined back to the post table here. But in this query plan, what looks kind of funny is the scan of the clustered index, the generating of the row number, and then a top, and then a filter, and then another top, right?

But the CTE isn’t to blame for the two references to the post table. I explicitly re-referenced the post table down here. So this is where you can see where I filter on the row number inside of the CTE, right?

That’s this thing right here, right? So the two tops in here, one is the one in this query, right? Where I get the top page number times page size.

That’s the first top. That’s this thing right here. Page number times page size. And then this other top where, ah, gosh darn it.

There we go. Where I’m just getting the top page size. So that lines up exactly with what I do here and what I do here. And then we saw in the filter operator, we saw this.

This is a really good way of writing page inquiries because you only hit the base table for a limited number of columns, filter down that primary key just to the rows that you care about, and then get all the columns you care about after you’ve reduced the rows that you care about.

So this is okay because we’re not re-referencing the CTE outside of this, right? We have them stacked up, which means that we hit the post table once and then run all the logic from these stacked things on the post table.

So where that gets different though is if you re-reference the CTE. So let’s say that I write a query like this, and this is an actual, this is inspired by an actual client query where they were doing this exact same thing, and their idea was to get the top, I mean, this was a different piece of software that had different things, but the idea of this query is to use the row number function to rate a particular user, sorry, a particular user’s questions by score descending, right?

So we want to find that essentially the top five, and the way that we’re doing that is for every, we left join to, ah, zoom it, curse you. We left join to the CTE to itself five times for five, and for each one, we get a slightly higher row number, right?

So we get row number one there, row number two there, row number three there, row number four there, row number five there. So when you see the query plan for this, you’ll start to understand what I mean by the petard hoisting of CTE.

So we’re derived tables, you couldn’t do that re-referencing. With CTE, you can, but unlike Postgres, which will, again, when it’s considered safe, materialize the result of a CTE, we don’t get that here.

We get a query plan, if I can manage SSMS, we get a query plan that hits the post table one, two, three, four, five times. For some reason, the first one is nice enough to go parallel.

The rest of them are just single-threaded scans of the post table, but each one of these, four seconds, four seconds, four and a half, oh, sorry, like four and a half seconds a piece. But the query as a whole takes almost, it takes about 18 seconds to run.

This obviously isn’t a good situation for the CTE. I don’t care how readable it makes your query. If it takes 20 seconds to get one row back, it stinks.

You shouldn’t just, it’s so readable. Who cares? Your query sucks and it’s slow. There are more important things to worry about. Like your end users aren’t going to call you up and say, hey, thanks for that nice readable query.

They’re going to say, hey, thanks for this really fast query result. I don’t have to wait my whole lunch break to get my report back. All right? CTE are not performance solutions. I don’t even think they make queries more readable.

Good formatting makes queries readable. CTE do nothing for query readability and do a lot to hurt query performance. So a lot to hurt query performability. How about that?

So you could do this in two different ways. You could create a temp table very cheaply with the five rows that you care about in it. And then you could join to that very small temp table five times in the same way that we did before.

And none of that takes anywhere near 20 seconds. You could also use pivot in this case. And you could get that very quickly as well. Again, with only one scan of the post or one scan of the post table.

So both of these end up just about the same. We do have to scan this, the temp table five times, but a five row temp table is pretty cheap to do that with. So there may be times when you need to build something recursive with a CTE.

And with recursive CTE, one sort of annoying thing is it in the recursive portion of the query, like the anchor portion of the query where you get like the row or rows that you want to build the recursion with.

You can do like almost anything in there. But with the anchor part of the query that you’re using to do the recursion down with, you can’t do anything like distinct or top or offset fetch to only get like one result per recursion.

But you can use a derived query inside of there. You can use row number inside of the derived query, and you can filter on the row number outside of it.

This is actually a pretty good performance thing for a lot of recursive CTE because a lot of the times when I see people build them, there’s a lot of duplication in them that shouldn’t be there.

So this is what I mean by that. We have the anchor part of the CTE here, and we have the recursive part of the CTE here.

Now, since we can’t put distinct or top or anything just like right in this query, like the way that we could write in a normal query, we have to nest it, right?

And we have to nest and do a row number in here and then filter on the row number outside of that. But you can do that in there just fine. And that’s one way to get around a lot of weird stuff that happens in recursive CTE.

So CTE can be handy to add some nesting to your query so you can reference generated expressions in the select list as filtering elements and where clauses.

Right now, SQL Server doesn’t have a qualify keyword that allows you to do that with windowing functions like some other database platforms do. They can even be good in relatively simple use cases. But remember, SQL Server does not materialize those results.

It would be nice if it gave you at least the option to. In the optimizer, it would be even better if it had some rules in place to automatically do it when it’s safe and you’re referencing a CTE multiple times.

I spend a lot of time in my consulting work putting CTE results into temp tables to avoid the re-execution problem that I showed you before and to reduce a lot of query complexity issues where bad cardinality estimates from a certain portion of a CTE leak out into other parts of the plan and lead to other bad choices elsewhere.

Materializing that result set and letting SQL Server build statistics on that result set is a really easy way to make a lot of queries go a lot faster. In complicated queries, CTE often do way more harm than good.

And any excuses that people feed you around readability are just throw them away. Again, it’s a stupid response. People will spend an inordinate amount of time trying to come up with cases where it’s okay to write queries the wrong way.

Mostly because they’re lazy and they want to just do the lazy thing and they want to keep writing queries the wrong way because they think they found this one magical time where it’s just okay and safe to do it.

And that’s almost never the case. You might find a query more readable by using CTE, but the optimizer will not. It does nothing to help the optimizer, does nothing to help guide it to better choices.

You can create, at least currently, some performance fences around things in CTE or derived tables by putting top or offset fetch in them, but that does not materialize the result.

It will sort of isolate that portion of the query, which can be useful sometimes, but there’s still no materialization. So if you put a top in there and you re-reference that CTE like I showed you in the demo query just prior, it’s a bad time.

So please use CTE really carefully. Don’t just assume that they are going to do anything best or better for performance.

Don’t think that they help the optimizer in any way. They really don’t, and you can run into a lot of big trouble with them if you start using them inappropriately. And it’s really easy to fall into that trap because you think you get this free lunch being able to re-reference them, but you just don’t.

So anyway, that’s about enough on that. This video went on longer than I thought it would, which story of my life. Again, I find myself having to apologize for the length here. So the next video, number seven, is going to be about views versus functions.

Very, very few demos in there. It’s a lot of spoken word poetry. So if you’re the type of person who really enjoys demos, maybe you can just skim that one a little bit.

But anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I will see you in the next video where we compare views and functions as a big happy family.

So great. Cool. 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.

How To Write SQL Server Queries Correctly: IN and NOT IN

---

Thanks for watching!

Video Summary

In this video, I delve into the nuances of using `IN` and `NOT IN` clauses in T-SQL queries, particularly focusing on how these directives behave when dealing with nullable columns. I share practical examples from my series on writing SQL Server queries correctly, emphasizing why `EXISTS` and `NOT EXISTS` are often superior choices due to their consistent handling of nulls and better performance. I also address the complexities that arise with `NOT IN`, such as the defensive query plans generated by SQL Server when null values are involved, which can lead to significant performance issues. The video concludes with a comparison between using `IN` and `NOT IN`, highlighting the importance of understanding these behaviors for writing efficient and reliable SQL queries.

Full Transcript

Erik Darling here, with Darling Data. Look at that nice blue. Really brings out the suffering in my eyes. Anyway, today’s video, guess what? We’re going to continue my series on how to write SQL Server queries correctly. And in today’s video, we are going to cover in and not in, because there are some funny things about these directives in T-SQL. But of course, before we get on with that, let’s talk about you and me, and you being nice to me for once. Mom. If you would like to sign up for a membership to my channel, there’s a link right in the video description that says become a member. You can do so for as little as $4 a month. If $4 a month would of, want. www, Eh?upg, not 100문in.

2015. *** buscar leads. directives to use in SQL in general. We’re talking about T-SQL specifically, but these rules will generally apply across all SQLs. So just really, you know, stick to exist and not exist as much as you can. In and not in, you know, if it’s literal values within, fine. But even if it’s literal values with not in, you have to be very careful about nulls. And you have to write your query defensively to explicitly remove nulls in order to get that working. Now, I did a performance focus video about not in, and I’m going to rehash a little bit of that. Again, oh, why aren’t you spelled out correctly? Oh, I hate when I use old code where I didn’t use the full word integer.

It’s very embarrassing for me. But I’ve already created two tables that allow, that are integers that allow nulls. And I’ve written two versions of, I’ve populated them explicitly from two different tables in the Stack Overflow database where no null values ended up in the tables, but the columns are nullable. Now, the big problem here is that SQL Server adds sort of like without even like announcing itself unbeknownst to you, adds a whole bunch of defensive stuff to the query plan in the event that a null occurs in the results. I’m going to show you what that looks like. So what I’m going to show you is this is the estimated plan for this. It does all sorts of things in here.

The estimated plan doesn’t really, like this is mostly just to show you that there is a lot of added complexity. We hit old users once, twice, three times, and we touch new users once. But the actual execution plan, you can see just how painful this was. This ran for over 20 minutes, 21 minutes, 22 seconds. And a lot of the problem in this query was, it’s a query plan pattern that is really, really bad. But SQL Server just inserts it when you use not in with nullable columns, right? See, like, you might see this query plan pattern in other places in SQL Server, but this is where SQL Server just throws it in there to protect itself. So you have this top above a scan. And like, if you see a top above a scan, and the scan is on a big table, you’re in real trouble. That’s never any fun, because this nested loops join is going to make this do a lot of work, right? So you have a lot of rows that come out of here, you have a lot of rows that go into the loop join, and you have a lot of scans of the old users table. That’s a bad time. Now, what I said about using not exist, like not having to worry about having perfect indexes, or writing really complex, or rather, adding complexity to the query to like, like hard code protection against nulls. Like, sure, I could add an index on old users on the the user ID column, or whatever I called it. And there would be a top above a seek, and that would be faster. But you would still run into the sort of logic, I’m going to call it a logical inconsistency, even though it’s consistent behavior. It doesn’t feel right to me for not in to screw up with nulls the way it does, or to handle nulls the way it does. So you could add an index, sure, and that would be faster. But now you have to worry about, you know, indexing your temp tables every single time you do this pattern. You could add a bunch of explicit not null checks on the old on the new users and old users table, you could write that query very defensively. Or you could not worry about any of that stuff, and just use the not exist version. This, that’s what I’ve done here, we say, you know, select the records from new users where not exists, correlate on that. And rather than taking 20 minutes, this runs in a few seconds, actually about two and a little under two and a half seconds.

So apart from the logical reasons for avoiding not in when with nullable columns, because, you know, let’s face it, a lot of people out there are afraid of making a column not null, because who knows, right, for the same reason that developers will make every string column varchar 255, or even a max data type, just because they’re afraid of truncation errors, or who knows what, even though the column is like state code, and you’re like, wow, like Massachusetts, New York, California, NACA, NYCTA, like none of these things are ever going to be 255. But who knows what will happen? Maybe, I don’t know, I don’t even have a reasonable thing to put in there. Maybe someone will like, say they live in every state or something, and you’ll have, you know, 50 times two, and that’s 100, but not even 255. Wow, that’d be real hard to do that. But developers screw things up constantly. That’s why I am a consultant with reasonable with reasonable rates, fixes these things. Okay, so anyway, you are far better off from both a performance and a consistency point of view, using exists and not exists over in and not in. Like I’ve said a few times now, it doesn’t really matter with in, because in does works the way that the same way that exists does, regardless of nulls.

So if you have a list of literal values or a subquery, you are safe using in. If you have a list of literal values or a column to column comparison with not in, and those columns are nullable, but don’t contain any nulls, you can end up with a really wacky query plan unless you explicitly filter out nulls with your query. And you have really good indexes in place to support that query.

With not in, if you like, if you just have a column and a list of literals, you still have to worry about that column, because any nulls in that column will make the list of literals from the not in clause misbehave, right? So just be like, be wary out there, exists and not exists are the better choice, because they both act consistently with probably what you would want to get back for your query results. Since there are no nulls, the first query returns correct results, but the amount of work SQL Server has to do to make sure that it doesn’t encounter any nulls or that it can behave safely if any nulls is pretty absurd, right? 20 something minutes to do that work sucks.

You can, of course, index the temp tables, but a lot of people have read one single blog post in their entire career about that and think that it’s always a bad idea. So it’s a separate conversation. But, you know, I’m going to be honest with you, I’ve had a lot of really good luck indexing temp tables in my life. So, what can I tell you? What can I tell you? The SQL Server has room for a breadth of experiences in when it comes to performance tuning. But anyway, I was going to say something else, but now I’m just giggling internally and I’ve lost my train of thought. So that was just about it for in and not in. Again, exists and not exists are usually far better options. Next up, I’m going to talk about CTE a bit. I actually have two videos coming up for CTE and they both sort of have different approaches to them. So there’s one that I’m going to talk about next and there’s one that I’m going to talk about, one that I’m going to go at the very end of the series. So there’s multiple contents on common table expressions because there is quite a bit to say about them. So anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something and I hope to see you in the next video about common table expressions, which is going to be fun.

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.

How To Write SQL Server Queries Correctly: Apply

---

Thanks for watching!

Video Summary

In this video, I dive into the world of SQL Server’s `APPLY` operator, specifically focusing on `CROSS APPLY` and `OUTER APPLY`. I explain how these operators can transform your queries to be faster and more efficient, especially when dealing with complex operations like windowing functions or derived joins. Whether you’re looking for top N rows, implementing row numbering, or performing pivot-like transformations, this video covers it all. I also highlight the importance of having appropriate indexes in place to leverage `APPLY` effectively, as well as discuss scenarios where using `APPLY` can lead to better performance than traditional derived joins. By the end, you’ll have a solid understanding of when and how to use `APPLY` to optimize your queries.

Full Transcript

Erik Darling here with Darling Data. Boy, am I hungry. It’s been a long day. In today’s video, we’re going to talk about, continue with my series, How to Write Queries Correctly. In this one, we’re going to cover the usage of apply. That’s cross-apply and outer-apply and different ways you can use cross-apply and outer-apply to make your queries faster and better and, I don’t know, do things like that. I like talking about apply. I like talking about apply. Hopefully, you’ll like me talking about apply. Hopefully, it just works out for everyone, right? Anyway, before we do that, we of course have some monetary concerns. We have to talk about fiscal policy here. If you would like to become a member of this fine channel, and maybe say thank you for the billions of hours of content that I produce, you can become a member. Join like, 30-something other people who have become members by clicking the first link in the video description down over there. It says become a member. If you are, I don’t know, if you are hawkish on your financial policy and you say, four bucks a month, Erik Darling, jeez, I don’t know. You can like, you can comment, you can subscribe. You can make my numbers go up in other places. Hopefully, it’s not going to be like blood pressure and cholesterol, because when those go up, bad things tend to happen.

Anyway, let’s not get grim. If you need help with your SQL Server, I am a SQL Server Performance Tuning Consultant. Some might even say they work at BeerGut Magazine, the best SQL Server Consultant in the world. I am available for all of these things and more. And as always, my rates are reasonable. If you would like to get access to my paid training, my paid SQL Server Performance Tuning training, you can get all 24 plus hours of it for about 150 USD.

And that lasts for life. It’s not a subscription. You have to renew every year. You buy it once and you have it. And then you can watch it and get better. And then you can be good at SQL, like me. Link, coupon code, done. No events. 2024 is done for events. 2025, I’m watching you. If there’s an event near you and you think Erik Darling would be good at that event, well, if you tell me what that event is, I’m not psychic. I have many things. I’m not psychic. I cannot magically guess which event you think I should go to. Let me know what it is.

And maybe I’ll be able to go. Who knows? I don’t know yet. Because you haven’t told me. All right. Now, let’s talk about apply. Now, I end up converting specifically a lot of derived joins, particularly ones that have windowing functions in them, to use apply instead for very specific reasons. Sometimes you need to create an index to support that thing. But mostly you want to avoid the eager index pool.

You need to at least be able to seek into an index on the inner side of a nested loops join to have that make sense. But particularly ones where row number is involved, it makes a lot of sense for reasons that I’m going to explain. All right. You will get a full explanation, but I just want to let you know that that’s usually where, for me, the apply stuff shines.

There are many other great reasons and places and things you can do with it. But that’s the one that I end up fixing the most. Now, when apply is most useful is if you have a small outer table and a large inner table.

Right. Because you want to have a small number of rows on the outer side of a nested loops join. And you can use that small number of rows to get to the inner side of the nested loops. Right. You don’t want nested loops for like you don’t want a big table on the outer side of nested loops.

And you don’t want two big tables involved with nested loops because you’re in for a bad time if you do. If the amount of work that like the query that goes into the apply is rather complex or does something that is computationally complex, windowing functions being one of those things, that’s another very good reason to use apply instead.

If I have very specific query goals that make apply pretty much the smartest way of doing things. Sometimes it’s like, you know, saying top three or offset zero rows fetch next three rows. Other times it is using a windowing function to filter out like where the windowing function is less than or equal to three.

Really, that depends a lot on data distribution density, stuff like that. That would be another good reason to use apply. If I am really trying my hardest to get a parallel nested loops plan, apply is usually a good way to do that.

If I need to replace scalar UDF in the select list with an inline UDF, that might be another good place to use apply. And if I need to use the values construct to do some surgery on one or more columns, that would be another good reason. We’ll talk through most of this stuff.

A lot of it is situational and it does require some practice to get familiar with it and know when the appropriate time to use apply is. Both cross and outer apply can be used in very similar ways to subqueries in the select list with the added bonus that, you know, like we took in the last video about subqueries. We talked about how you can really only return one row with them and you can only return one column with them.

With cross apply and outer apply, you don’t have those limitations. You can return multiple rows and multiple columns. That’s why I like the top end per group thing is really popular for apply.

What you really want to think of when you’re choosing which apply to use is cross apply really should be called inner apply because it’s like an inner join. And outer apply is actually appropriately named because it’s sort of like an outer join, right? Outer apply does not restrict rows.

Cross apply does. So here’s here’s sort of a simple example. Now you could use top three. You can use offset fetch in here. But let’s say that I just wanted to get the top three user the top three posts for a user.

I can do that with with this query pretty easily. Now. These are the these are the results.

Some of them. You will have three. Some of them you won’t. There might not be three for everyone. But for the people who do have three, you will get them. So that’s nice.

Right. There’s not really a great way to like insert a dummy row if if you just want like a third thing to show up for everybody. But no, whatever. Neither here nor there.

You can also use row number to do something similar if you don’t have good indexes in place or if your data distribution just sort of it just makes more sense to to use row number instead. There are some pretty good reasons to use row number if you are on if you’re in a higher SQL Server compat level where batch mode on rowstore is available. Or if you like, you know, can get batch mode involved using a trick with like a temporary table or an empty filtered non clustered columnstore index.

Because you can see the window aggregate from batch mode show up. And that’s a lot faster than the typical arrangement with row mode windowing functions where you’ll sometimes have a sort to put data in order for the partition by order by. And then the segment segment the sequence project.

The window aggregate is a lot usually a lot faster than the like the batch mode window aggregate is a lot faster than the row mode equivalent of those plans. So there are lots of good reasons to use apply depending on like and we’re, you know, we’re using apply in both of these just this one’s with row number and this one is you can again, you can use top or offset fetch. What what really drives the decision here is if this is fast enough, then cool, use this.

If this if that’s not fast enough, then you might want to think about using row number instead of the top with offset fetch. Really, like I said, it’s going to depend on like data distribution density, things like that. And, you know, getting like like top and offset fetch don’t really get like the batch mode benefit that windowing functions do.

So if if batch mode is a goal, then the windowing function will probably be faster there as well. So like for those queries, we’re just getting everyone from the users table who posted a question in the final days of 2013 ordered by when it was created and reputation and some other stuff. But the I guess the point is that this produces essentially a tabular result, right?

This produces like a second table that you’re joining to. And for everything that we find in here, for every row that we find that that meets this criteria, we apply this logic to every row. Right. So we can get multiple columns back.

We can get multiple rows back. We can get lots of stuff back. Now, one thing that I think is probably worth pointing out is like when we were talking about exists and not exists, one thing that I said is that it doesn’t matter if you what you put in the select list of exists and not exists because SQL Server just throws it away. Okay. Sort of in the same vein, it’s okay if you use star in apply or outer apply in the inside of the applied part of the query, because whatever columns you actually pull out of it up here, those are the only ones that the SQL Server is smart enough to realize you’re not selecting every column out of the post table.

SQL Server does some figuring when you first send it a query and it says, oh, even though there’s a select star in here, I know that in the outer select, I’m only getting title score creation date and last activity date. So I know that I like, I don’t actually need to treat this like a select star query. So using the select star inside of this is not a big deal because I’m not using select star in the final outer select slash project front for the results.

So that’s nice there. It’s sort of like how I have a select star here and a select star here, but SQL Server is like smart enough to realize that only these columns are involved aside from, you know, the stuff that I’m using inside of the query. So because the user’s table is correlated from ID to owner user ID in the post table, we do need to make sure that we at least have a good index that leads on owner user ID. So for every trip that we, for every time we apply that query to what’s in the user’s table, we have an efficient way to seek into that index and find the rows that we want.

You’re going to have some additional considerations with the windowing function thing, or if you have a top or offset fetch with a, with an order by in there, because you’re going to want to figure out how to not sort data every time, probably. Right. So just a couple notes on that. Another neat thing you can do with apply is sort of, you can do like a mock pivot and unpivot with apply. Itzik Ben-Gan has a lot of great videos on this. If you’ve never seen him present on apply, I would highly suggest just looking, looking for either, you know, his blog posts about apply or his videos about apply.

Pretty much anything where he talks about T-SQL is magical. But one thing that you can do is you can use this cross apply with a values clause to sort of combine the creation date and last activity date columns like this. And you can use that to sort of like pivot on them like this, like you’re turning each of these, you’re turning each of these, like each of these columns into a single column, right? So creation date and last activity date are two separate columns, but using values, we can pass them in as a single column and we can do, we can mimic the greatest and least functions that SQL Server 2022 added.

So like with SQL, if you’re on SQL Server 2022, we could just use greatest and least to figure out which value is higher or lower, the greater or the lesser. But with older versions of SQL Server that were the greatest and least functions aren’t available, which is weird because they’ve been around in other databases like forever. You can do something like this and this ends up with a pretty neat and nifty query plan.

We get all the stuff that we care about out of here. You’ll just have this constant scan, which you’ll see has just about twice as many rows as this because we need to basically make one long list from creation date and last activity date. And then we just aggregate to figure out the min and the max from those.

So you can use apply with the values clause for a lot of really powerful stuff. The choice to use apply really does depend on the goal of the query and the goals of the query tuner. It’s not always a magic performance tuning bullet, but under the right circumstances, it can really make things a lot faster than doing something like a derived join.

The choice of cross apply or outer apply, of course, comes down to query semantics. If you want the apply to restrict rows or filter rows, you want cross apply. If you want to do the equivalent of like an outer join, then you want to use outer apply.

One important difference in how the joins are implemented is in the optimizer’s choice between normal nested loops where the join is done at the nested loops operator and the apply nested loops, which is when the join keys are pushed to the index seek on the inner side of a join. When you see that, when you get apply nested loops, you can tell because when you highlight, when you hover over the nested loops join, you get the little tool tip that pops up.

Down at the bottom, you’ll see something that says outer references. And those outer references are the seek predicates being pushed into inside of the nested loops join rather than having them apply at the nested loops join. There’s a great post by Paul White about apply nested loops.

Again, if you’re feeling googly, definitely look for his post on Paul White apply nested loops because you’ll learn a lot about that there. Now, the optimizer is capable of transforming an apply to a join and vice versa. It will generally try to rewrite apply to a join during initial compilation because there’s more searchable plan space for that type of join.

If you transform to an apply early on, it may also consider a transformation back to an apply shape later just to figure out what would be cheaper. But just writing a query using apply does not guarantee that you get apply nested loops instead of just regular vanilla nested loops. Having good indexes in place is really like generally what tips the optimizer towards using that.

Now, there are a couple of things that, well, because we was talking about Itzikbengan earlier. There are a couple of cool things that you can do with apply that make life a lot easier. One of them is kind of what I showed you with greatest and least, except you can expand that to do lots of fun things, right?

Like finding the min and max per user. This isn’t a terribly fast query, but that’s okay for this one. We just start off, we start off by doing sort of what we did in the first query where we get sort of an initial min and max from things.

And then we use a slightly, I mean, it’s not even convoluted. It’s just something that we have to do some additional aggregations out here to have that group by ID and display name. Otherwise, we would have multiple rows, right?

We would get multiple rows back. Because, again, not like exists where you just get one thing. You know, like basically for every post in the users table, this is going to generate a row. So users who have multiple posts will generate multiple rows and we want to collapse that down just to get the min and max.

Another really cool thing that you can do with cross apply and continuing to use the values clause is you can sort of like, again, this is something Itzik talks about in his things. I think he’s right that this is a very neat trick, is you can do stuff like get the year that each of these things happened in.

All right. We have like the creation year and the last access date year. And then we could just assemble like the beginning and end span from that.

So we can use date from parts to take a year and just say 0101. Oops, that didn’t go well. Just say 0101 here and say 1231 here.

And we can get sort of like a span of time. So, I mean, some of these are more interesting than others, right? Like, well, like a lot of these are 2008 through 2018.

So it doesn’t really show off how cool this is. But this one, you know, we get the correct 2008 to 2017. Where are some good ones in here?

I don’t know. Some of these query results are just boring. But very neat things that you can do with apply and with values that can actually, and I’m going to talk about this when you get into CTE a bit more. With CTE, you typically have to like keep stacking them.

And, you know, the more complex things you have to do, the worse that stacking gets and like passing results and aliases down from one to another. But with apply, you can generally do stuff a lot more cleanly without having, without the fear of CTE executing the query inside them more than once and causing a giant cascading awful of query plan. So just a little bit about cross apply and outer apply there.

Very, very useful query techniques for all sorts of things. Most common use is sort of a top and per group thing. But, you know, there are lots of other cool uses for them that, you know, really, if I had all day to talk about apply, I could show you a lot of things.

But trying to keep this sort of short and basic so folks understand kind of what they are and how to use them. I don’t want to overcomplicate that and get into the really crazy stuff because I would probably lose a lot of people. So I don’t want to lose anyone.

I’ve lost enough in my life. I don’t know. We don’t need to talk about that. But you know who you are up there. Anyway, thank you for watching. I hope you enjoyed yourselves.

I hope you learned something. And I will see you in the next video, which is going to be about what’s number five here? In and not in.

So we’re going to have some fun things to say about in and not in in that video. So do try to contain yourselves. But I understand why some of you out there just might be orgasmic.

What we’re going to say in the next one. So I will see you in that video. Goodbye.

Not forever. Just until next time. Goodbye for now.

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.

How To Write SQL Server Queries Correctly: EXISTS and NOT EXISTS

---

Thanks for watching!

Video Summary

In this video, I dive into the world of SQL Server queries, focusing on the often-overlooked `EXISTS` and `NOT EXISTS` clauses. These powerful tools can significantly improve your query performance by reducing duplicate results and making your code more efficient. I share practical examples and explain why using these directives correctly is crucial for writing effective SQL queries. Whether you’re a seasoned SQL professional or just starting out, understanding how to leverage `EXISTS` and `NOT EXISTS` can greatly enhance your ability to write optimized and maintainable queries.

I also take some time to address common misconceptions about these clauses, particularly the idea that what you select in an `EXISTS` clause matters for performance. I demonstrate with examples that selecting anything—like one divided by zero or even the entire King James Bible—has no impact on the query’s execution plan. The key is understanding how SQL Server processes these clauses to find matches or non-matches, and why using them properly can lead to more efficient queries.

Full Transcript

Erik Darling here with Darling Data, and we are getting into video weird green screen. I don’t know why that’s happening behind me like that. I think I need to adjust the light over there a little bit. But in this video, we’re going to continue the series on how to write SQL Server queries correctly. And in this video, we are going to talk about the wonderful, the fabulous, the underused, the malnourished, the oft overlooked, exists and not. The number of times I have tuned queries just by changing some paradigm joins in, not in, things like that to use exists or not exists is amazing, quite frankly. So I think you’re going to enjoy this one. But before we do that, we need to talk about how much child support you owe me. Just kidding. If you like this channel and you want to become a member, there’s a link right in the video description. It says something like, become a member, you can choose to pay me $4 a month in child support if you like the video babies that I’m cranking out. If you cannot afford the $4 a month, of course, you can like, you can comment, you can subscribe, you can, you know, show your affection in other ways. I guess it’s sort of like a rich dad, poor dad or something, but you know, whatever. If you need help with SQL Server, in any, for really anything, you have the best SQL Server consulting in the world available to you, and I can do any of these things at a very reasonable rate. I can also do more stuff, depending on sort of what you’re what you’re into and what you need. But these are these are the things that I usually end up doing with people. So I’m pretty good at them at this point. If you need some very high quality, very low cost SQL Server performance tuning training, you can get all of mine, again, via link in the video description that talks about that says, like, buy training, or something, or something, or something, and you can get 75% off. That brings it down to about 150 US dollars. And that is for life. That is not an expiring thing. The only thing expiring is you. Video training will be there until, I don’t know, hard to say. Forever, maybe. I mean, as long as it’s useful. Yes. No upcoming events, end of the year, 2025.

We’ll figure it out. With that out of the way, let’s talk about exists and not exists. Now, I think what’s great about SQL is the structured query language is designed in a way where certain directives is very, very obvious what you are getting when you use them. exists and not exists. You can say, I want to find things where they exist, or say, I want to find things that don’t exist. I don’t know. I think these are wonderful things. What if you were able to find aliens like that, or like a multiverse or something?

All sorts of interesting things could happen. The sort of awful thing about SQL is that it has a lot of rules. And they are selectively applied, just sort of like the English language itself. I have a young daughter who is learning how to read and explaining to her various rules for spelling and pronunciation and grammatical correctness is… It’s a fun challenge. It’s a fun, fun challenge. I, of course, have, you know, my gripes and grievances with SQL.

If you want, like, the pettiest example, I think that instead of select, we should just write get. Not only is it half as long, but it’s far more obvious what you’re doing. When I go to the store, I do not select milk, eggs, steak, butter, salt, pepper, scotch, anything like that. I usually just get them. I get the things that I want. But, you know, that’s, you know, that’s my breakfast of champions. I don’t know what yours is.

But two of the most overlooked things in SQL are exist and not exist. Perhaps they would get more traction if they were called there or not there. But I think if you had to deal with a where clause and a there clause, things would get real weird real quick. But it might be kind of fun to say, like, select star from table where there is not or where there is or, you know, something like that.

Where something is there or not there. I don’t know. It would be fun for me anyway. But whenever I bring these up, people get kind of weird about them because they’ve usually read some, like, really incorrect blog post at some point in their life that says subqueries are bad. And, of course, exists and not exists work off something that looks pretty well like a subquery.

And they’re like, no, can’t do that. Bad, bad, bad, bad. But those people are fools and they read things by fools. And then now we’ve just multiplied the number of fools in the world.

This is the problem with people writing foolish things is that foolish people read them. So you have foolish people writing foolish things that foolish people read and become doubly foolish. The foolish is just exponential.

What is it? Phrase it something. Exponentially caustic foolishness in the world. Now, I think the fun thing about… That shouldn’t be there.

You get out, you idiot. Foolish thing. The nice thing about exists and not exists, and this is something that comes up quite frequently when we’re talking about using these directives in SQL, is people think that something, whatever you put in the select list of the exists makes some difference to performance.

It does not. You can put select star. You can put one divided by zero, which is my favorite thing to do to prove that it doesn’t mean anything. You could put the entire contents of the King James Bible in there.

And guess what? Wouldn’t make a difference. SQL Server throws it away, forgets it ever existed. Likewise, if you add distinct or top or anything like that to an exist clause, it doesn’t matter.

Offset fetch would be another row filtering thing. It doesn’t matter. Group by doesn’t matter, right?

Because SQL Server does not work, does not care about that. It only goes in to find a match or not a match. And there is a one-row goal on that anyway.

So it doesn’t care about that. One thing that we talked about in the joins video is if you have a one-to-many or a many-to-many relationship, SQL Server will show you the results when you use join with exists and not exists. We only care that a row is there or a row is not there.

Right? Don’t need to find… If we find, like, let’s say we have user ID 1 here and we have 10 rows that match user ID 1 here, we say where exists this.

SQL Server does not say, oh, I found one. Oh, I’m going to go find nine more ones. It just says I found a one. We’re good. If we say where not exists and the SQL Server is like, oh, wait, but I found a one. It’s going to say that one is there.

It’s not going to go find that one nine more times to make sure that it still doesn’t exist. Right? So we find one or we don’t find one and we bail out. So both exists.

We already talked about that. Now, let’s say you are a brand new query writer, you know, doing your thing in the world and you have been tasked. Your boss says, hey, pretty please, give me a list of people, of IDs and display names from the users table who have made a post, who have a reputation of one.

Right? What stinks is that if you were to write this query and, you know, the most straightforward way possible, you would get a whole bunch of duplicates back. Why?

Because there are a whole bunch of people who might have made multiple posts, who all have an ID of one. There are a lot of people in here who have that problem. Right?

I mean, just think about how many rows matched community up here. Lots of them. So we’re going to see lots of duplicates in here. Right? Here’s May Taha with like five rows of duplicates. You look at that and you say, ah, boy, I don’t feel like sifting through all that.

Ah, group by, I’d have to write two column names in there. I don’t feel like writing all that. Typing?

For idiots. Typing. Fingers get tired. I’m old. I’m just going to throw distinct up at the top. Okay. Well, you can do that.

And, I mean, the query itself doesn’t actually run that much faster, but we do get the results back faster because we have, we send fewer rows to SSMS to process and put into grid form. So, like, the query plans and the performance don’t matter much here. But you could do this and you could, you know, very easily get back the results that you want to see.

Which is fine. But, uh, that only works kind of up to a certain point performance-wise. After a certain point performance-wise, uh, throwing distinct, especially on a very long column list, uh, can, can, can become pretty painful.

I would, I would strongly advise against using distinct for long column lists. I would strongly advise in favor of, uh, you know, uh, either getting that distinctness some other way. Like, you could add, um, like, you, you might know that there are two or three columns in the results that make up a distinct, uh, tuple.

And you could use, like, a row number or something to just include those three columns to figure out dupes and then filter to where row number equals one. Uh, you could also write the query slightly differently so that, uh, duplicate results are discarded the very moment you start joining rows together. Right?

So, uh, let’s say that we wanted to write this query and we wanted to just get the distinct results from users that have a matching row in posts. Well, that is exactly what exists, exists to do. All right.

So if we run this query and we, uh, get this stuff like the same way that we did before, uh, we’re going to get an execution plan that has, well, this one’s a little misleading. Usually when you write a query that does this kind of thing, you will see, uh, a semi join or, uh, for exists or an anti-semi join for not exists. This one just works a little bit differently.

Uh, and this one just kind of groups by, uh, the post table. Uh, it aggregates all those rows. So there’s only one of each. And then we join a distinct result set of owner user IDs from posts.

All right. Uh, ooh, there we go. The emerge join to the users table. Um, if we throw, let’s just so I can show you the semi join version of this. Let’s say option force order and let’s get an estimated plan for this.

Oh no. It does the same thing. It just reverses it. Nevermind. Okay. Forget that happened. No query plan for you.

Um, so once exists locates a match, um, then it’s, it’s like, cool, we got this row. I’m going to return that row, right? It’s basically inter joining the tables together.

Uh, I see a lot of people attempt to write exists queries. The same way that they attempt to write in or not in queries. And again, this comes down to the column list that comes out of exists does not matter.

Right? Because notice, we notice what we’re missing here. There’s the, we had it up here. This, this was correct.

Right? We, we selected nothing. We selected one divided by zero from the post table, but we had this correlating where clause. Right? So where the owner user ID here matches here, I see a lot of people try to do this and it just doesn’t go well because there’s no correlation in here. So if there’s for every row and posts, SQL Server is like, yeah, yeah, there’s something’s there.

Okay. We, we, we get a thing and then SQL Server just basically gives us a, a, a, a left semi join with no join predicate, which is not what you want. This query is incorrect.

If you write your exists or not exist queries like this, you will be sadly, you’ll be very sad about the results because, um, they won’t, they won’t be right. They will be completely wrong. Right?

So make sure that when you write your exists and not exist queries, they are properly correlated inside of the exists and they do not just look like this because this is bad and wrong. We do not want this happening. Now, one thing that grinds my years, you know, it gets, gets me fired up, angry at the world.

Uh, I want to, I want to drink that 24 ounce glass of scotch, go out, go out there, the baseball bat is whenever I see a SQL tutorial, uh, they give this advice about finding rows in one table that do not exist in another table. And they, they, they seem to all, uh, uh, seem, all seem to favor using a left join to do that. So the basic query pattern, and I’m not saying that you should never do this.

There are times when this will be the better choice, when this will perform better. It is, uh, up to a lot of very, very localized, um, uh, things around like indexing and stuff like that. Uh, but in like, you know, SQLs and the optimizer making good join choices based on good cardinality estimates and things like that.

So things like indexes, the cardinality, uh, estimator you’re using legacy or default, legacy or new or default or new or whatever, old or new, let’s just say. Uh, lots of things can mess up how SQL Server chooses to do these sorts of, how to do these sorts of joins, how to, which physical join operator they choose. Um, so there’s all sorts of things that can make one choice or the other good or bad.

But the basic thing that you do is you left join, you select from the users table, and then you left join to whatever other table. And then you find, typically you use the primary key, um, ID is the clustered primary key in the post table. This is the most common thing that you’ll do.

You could use, technically you could use any non-nullable column that you want, but, you know, clustered primary key is a pretty good choice for that. The problem that you run into is that when you, when you use this pattern, what SQL Server does is it takes both tables, right? We have users here and we have posts here.

SQL Server does not choose for this query to do any early aggregation. Uh, that makes sense for the users table because ID is the clustered primary key. That probably makes less sense for the owner user ID column.

Remember when we did the exist query and I showed you, it did that, uh, aggregate from the post table. So there only one row would come out of that. Um, you know, SQL Server is just like, okay, well, no, uh, no early aggregation for you here.

Uh, we fully, you fully joined both tables together. And then after those two tables get fully joined together, then you filter out rows, right? This is where we start.

This is where we start reducing the result set in our where clause. And if you hover over the filter, you’re going to see that our predicate for that filter is where the ID column from the post table. Again, the primary clustered key is null.

And this is usually a pretty bad, this is usually a pretty bad query pattern to see because you want SQL Server to filter out rows as early as possible. Not join every single possible row and then filter out, uh, rows that don’t match. So, uh, a better way of writing that is of course to use not exists because not exists will tell you which rows aren’t there.

And if we run this, remember this query runs for about 1.6 seconds. Uh, we run this one. This one finishes up in about half the time, about 800 and some odd milliseconds.

But notice we have the different, we don’t have, we have all, we have a fairly close pattern here. Not exactly perfectly, you know, aligned, but, uh, SQL Server does opt for the early aggregation on, on, from the post table, right? So it makes a, it does the aggregation on the owner user ID column.

And now we have a different type of join, right? We don’t have just an outer join with a filter afterwards. We just have the aggregate that does the count afterwards. But we actually have up here is a left anti-semi join, right?

So this means that the rows get eliminated at the join rather than fully joining the tables and filtering them out later. So a lot of times when I’m tuning queries and I see that pattern with the left join where some column is null, like one of my first instincts is to replace that with not exist to see if that gets us any sort of performance improvement.

Most of the time it does, not every single time, but most of the time it will. Um, so, uh, your developer life will be a whole lot less confusing and tiresome, uh, if you make sure that you fully take advantage of all of the things that SQL Server has available to it. Um, the real tough thing, the thing, something that bothers me quite a bit about, um, you know, certain frameworks, like ORMs and any framework being one of them, uh, is that it’s not always obvious, or rather it’s not, it’s not always done correctly where, uh, a join or exist is used where it should.

They are capable of doing it, but it doesn’t always happen. And, and there’s different ways to write those types of queries so that if you’re doing, uh, joins to either find just the existence of something like, say like just to do, like you’re doing the join for the purpose of filtering, not for the purpose of displaying data. If you just need to find rows that match from one table to another, or rows that don’t match from one table to another, exists and not exists are usually the more efficient choices there.

Um, that changes, of course, if you needed to bring data back from the table as well, right? That’s when you would want to use a join because you can’t project data out of exists or not exists. That’s why the select list up here doesn’t matter.

So just kind of keep that in mind when you’re writing queries, uh, that, you know, like you can’t project anything out of exists or not exists. So if you, if we needed columns from the post table, we wouldn’t want to use that. We would, that’s where we want to use a join instead.

But if we’re just looking for rows, there rows, not there exists and not exists will usually get you there faster. Now, uh, the next thing we’re going to talk about in this series is, uh, subqueries of the correlated variety. Um, suppose non-correlated subqueries are a bit more exotic and a bit less useful, but, uh, that’s what we’re going to do.

So, I hope that, I hope that that excites and titillates you and, uh, you’ll stick around to watch that. So, I’m going to do that one next. So, thank you for watching.

I hope you enjoyed yourselves. I hope you learned something. And, uh, what else is in there? I hope that you can’t hear the sirens currently going by. That would be nice.

Um, I’m actually going to, going to listen to this video, the very end of this video, uh, to see if, if the sirens show up on the recording. Because, you know, life in the big city. All right.

Anyway, uh, let’s get going here. I’m just, I’m just, just going on at this point. 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.

It’s Been A Wonderful year

---

Rest up, 2025 is gonna be a fun one.

Video Summary

In this video, I dive into the world of SQL Server maintenance and optimization, focusing on a common scenario that many DBAs face: dealing with server restarts and their impact on performance. We start by discussing how server reboots can affect indexes and statistics, leading to potential performance issues when queries are executed after the reboot. Then, I share practical solutions and best practices for minimizing these disruptions, ensuring your database operations run smoothly throughout the year.

Full Transcript

Hey, hey, it’s New Year’s. Happy New Year. Go back to bed. It’s time.

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.

How To Write SQL Server Queries Correctly: Subqueries

---

Thanks for watching!

Video Summary

In this video, I delve into subqueries in SQL Server, addressing common misconceptions and providing practical insights. I start by challenging the notion that subqueries always run once per row, explaining how their performance can vary based on the join type chosen by the optimizer. Throughout the video, I share examples of when to use subqueries effectively, emphasizing the importance of good supporting indexes for optimal performance. By walking through a detailed query example, I demonstrate how eager index spools in the estimated plan can indicate potential performance issues and show how forcing SQL Server to use specific indexes can significantly improve execution times. The video also explores scenarios where collapsing repetitive subqueries into a single apply operation can enhance efficiency, although this isn’t always necessary or beneficial. Overall, it’s a deep dive into understanding and leveraging subqueries for better query optimization in your SQL Server environment.

Full Transcript

Erik Darling here with Darling Data. And it’s just, you know, me and Bats here, kicking it. Feeling real twinsy here. Mwah! Love you Bats. In today’s video, we’re going to talk about Intel updates getting the hell off my screen. Intel. What can Intel get right these days? That’s the big question. Anyway, in today’s video, we’re going to talk about subqueries. Oh dear. Now, in the last video, I did foreshadow this a little bit because we were talking about exists and not exists. And how a lot of people who I end up working with think that exists and not exists are bad because they’re subqueries. And they just have this thing against subqueries. So like subqueries, oh, they always execute once per row. Oh, they’re slow. They’re not as good as joins. They have these preconceived notions. They have these preconceived notions that certain things are just always true across every query ever. And that they know best and better. And they have some kind of authority in the matter. They’ve tested every conceivable query that you could ever write with every index and every database and every cardinality estimation model. And they just know everything is always true the way that they’re going to be.

They’re often wrong. And that’s what we’re going to talk about today. So before we do that, let’s talk about money. Everyone’s favorite subject, unless you ask them how much of it they have, because then things are good. That’s when people get uncomfortable. If you would like to become a channel member, you can do so for as little as four bucks a month. It’s just a nice way to say thank you for all of the content that I produce. You can do so for as little as five years. You can do that by clicking in the link in the video description that says, like, become a member. Cool. If you don’t feel like giving me four bucks a month, you can like, you can comment, you can subscribe, you can make different numbers go up, which also always fills my heart with joy and, you know, makes me feel less alone in the universe. If the things I’m talking about during these videos make you think, wow, that Erik Darling sure is good at SQL Server, you’d be right.

And I’m also available for consulting in SQL Server matters like these. And as always, my rates are reasonable. If you would like some very high quality, very low cost SQL Server content that you can buy once and will continue to be yours and you’ll have access to it for the rest of your life for about 150 US dollars, you can use the link in the video description. Or you can go to that URL right there and put in that discount code and you can get all 24 hours of my performance tuning training for the one time low cost of 150 bucks. No upcoming events. No upcoming events. It is the end of the year. I have no interest. 2025. I will have lots of interest. Anyway, let’s talk about sub queries because they’re a lot of fun.

So we’re going to talk about sub queries in the select list. I like them because you can use them to skip a lot of additional join logic. When you write a join in a query, the optimizer will do all sorts of funny things, wondering about where that join would best be placed in your query plan. It doesn’t really do the same thing with sub queries. Now, when people start saying that sub queries run once per row, they are not entirely wrong. But the thing is, if you write a join, that join might also run once per row too. If you write a sub query and SQL Server uses a nested loops join, and let’s just say that your query returns 1000 rows.

Yes, that sub query will run 1000 times to produce a result in a nested loops join. If you write a join that does whatever your sub query wants to do, and SQL Server chooses a nested loops join, your query might actually run, your sub query might actually run, or sorry, your join might actually run a lot more times. Because that join might happen way earlier in the query plan. And let’s say, rather than just running 1000 times at the end, what if it gets joined to a big table, and it has to run lots of times? Hmm. Gosh. This sure do get confusing.

So when people say things like, sub queries run once per row, well, lots of things can run once per row. It all depends on the type of physical join that the optimizer chooses to implement that semantically correct query logic physically in the query plan. Hash joins, merge joins, they typically do one big seek or scan of an inner table, bring a bunch of data back.

Nested loops joins, you know the algorithm, take a row from the outer input, send it to the nested loop, go do something in the inner input, grab a row here, bup, bup, bup, grab another row, bup, bup, bup, that is also once per row. No. Ah. Ha ha ha. My rates are reasonable.

Anyway, sub queries do have some limitations. There are times when you can’t use them, which is okay. Everything’s got to have limitations. I have limitations.

I don’t know anything about Oracle. I don’t know anything about cheap wine. Except not to buy it. I don’t know anything about…

I don’t know. What’s it? That’s something else. Let’s go on. Anyway, but if you use sub queries in the right way, they can be an excellent method to retrieve some calculation result without worrying about what kind of join you’re doing, and how the optimizer might try to throw that join into the mix of, let’s face it, I know your queries.

There are 42 left joins and 11 inner joins and 13 cross joins already. Throw another join into the mix. Why not?

Well, it could get worse, you buffoon. Since sub queries are in the select list, it is sort of like doing an outer join because the sub query is not actually allowed to eliminate any results. It takes the whatever, however many rows are going to get projected from everything from like the from down, your from join where, group by, stuff like that.

And it takes that result and it says, sure, for every row that I’m going to get out of this whole jumble of things that user x just did, I’m going to go run this query to get a result for the row, which could be a nested loops join or it could be not a nested loops join. But either way, it’s going to be an outer join because it’s not going to filter anything from the results.

So the optimizer doesn’t have to care as much about where that join gets placed. It’s usually going to be way to the left in the query plan because that’s where that’s the left outer join that’s not going to eliminate anything goes because we need to get all the stuff that we’re going to actually do stuff for first. So that’s good.

It’s all good, wonderful things about sub queries. And the optimizer is generally smart enough to retrieve data for the select list sub queries after all the other joining and filtering is done. So they can be evaluated for as few rows as possible.

There are, of course, bad ways to write queries that might end up with a query plan that contradicts that statement. But I’m not in the business of writing bad queries, in the business of writing good queries that run fast. So the most important thing that you can do as a developer, if you’re going to write sub queries, or really almost any kind of query in general, is to make sure you have good supporting indexes for them.

So this is where we’re going to talk a little bit about performance before we talk about other stuff. Now, I’ve already created good indexes to support my sub queries. I already have them.

But what I want to show you is what a query plan will look like. I’m forcing SQL Server to use the clustered index for each of these three sub queries. What I want to show you is what a query plan will look like when sub queries in the select list are going to be slow.

So let’s just get an estimated plan for this. And the important thing that I want to show you is over here. If you have a query, we’re talking primarily about sub queries in the select list, where you can see this happen.

But if you have any query, ever, and you look at the estimated query plan, or you’re looking at something from the plan cache or something in query store, and you see eager index spools like these, this and this, being built off a large table, like, say, the POST table in the Stack Overflow database, that means that this query is probably going to be a lot slower than you would hope. This query is not going to be in for a good time.

SQL Server is using a single thread to scan the POST table once there and once where my head is. So it’s scanning that twice. It can only use a single thread because it’s building an eager index pool here and here. So this isn’t actually even going parallel.

Sorry, this and this aren’t actually even going parallel. Even though they have parallelism operators on them, you can only build an eager index pool with a single thread. This is Microsoft, once again, kicking standard edition users when they’re down because they never want you to be able to build an index in parallel.

No, nothing for you. So that happens, right? Like, this is what a query plan will look like when sub queries are going to be bad.

Always keep an eye out for this. No matter what kind of query you’re writing, if you see eager index pools being built off large tables like the POST table, you’re either missing an index that would really help SQL Server, or the optimizer is making a buffoonish choice and you need to add in a force seek hint.

Because the optimizer will sometimes build an index pool off a perfectly good index that it could have seeked into. I brought this up to Microsoft and Microsoft does what it usually does and shrugs and says, Oh, sorry, we spent $70 billion on AI.

We can’t fix basic stuff in SQL Server. So that’s cool. Anyway, let’s move on. And let me show you, I’ve quoted out the index hint here. So now SQL Server is going to be free to use the indexes that I have created that are good for our sub queries.

And what you’ll see is that SQL Server does indeed choose nested loops. So these sub queries do run once per row. That does happen.

It do be like that. But you could write any kind of join. You could write apply. You could write cross apply. You could write outer apply. You could write a regular join. You could write a derived join. And SQL Server might choose nested loops for it.

In which case, it would run once per row anyway. It gads. I’ve been gapped. Anyway, if we run this query, and I’m actually going to give, oh, you know, I should turn on query plans.

That would help, right? If we run this query, I want you to note that it runs very quickly. We don’t spend a long time doing anything in this query. In fact, if we go look at the execution plan, it finishes in 23 milliseconds.

This doesn’t feel like sub queries being slow to me. This also doesn’t feel like there being a very big penalty for three sub queries running once per row.

We don’t really do much of anything in here. It’s just this final sub query that does a little bit of extra work. These seeks are very fast.

We get zeros in here. It’s just this final count one that does, you know, any sort of work. We get 22 milliseconds of work across those two operators. So that’s really not all that awful.

All right, this is a fairly quick query. Now, there are times when you have sub queries that have very, very common sub expressions, right?

Like all of these queries are doing something pretty similar. All three of them are correlating on the post type ID column, right?

But this is looking for post type ID one. This is looking for post type ID two. They’re all correlating on owner user ID equals user ID. They’re all three of them are doing that.

And all three of them are going to the post table. So there are times when very repetitive sub queries like this can be collapsed into a single apply and can be faster.

There absolutely can happen. Not going to BS you on that. But typically, that is when the sub query is rather complex.

What I see a lot in some client query plans when there’s a lot of complexity like this is let’s say that the query runs for one second.

And let’s say there are 100 operators in it, right? In the query plan because it’s a big complex query plan. And I’m running to get the actual plan and I start looking at operator times.

And there’s not really a single part of the plan that really contributes to that one second. There are lots and lots of little parts of the plan that all contribute to it taking one second.

When I see that and I need to make it faster than one second, my goal is to reduce the complexity of the plan which can, which in part of that is reducing repetitive sub queries in the plan or repetitive, just let’s just, let’s just even go a little further than that.

Let’s just say collapsing repetitive expressions in the plan. That can be a really useful trick. Okay. So I’m like, no, like you can, there are times when that makes sense to do.

This just doesn’t happen to be one of them. So if, if let’s say that, you know, like we, like we look at this query and we think, oh, I think that those, all three of those sub queries are cut, like, you know, we don’t, we 23 milliseconds.

We need it to be faster. Now let’s say that we wanted to be, this to be quick. And let’s say we, we had a mental problem with making three round trips to the post table to do the stuff that we were talking, that we were doing up there.

We have two ways that we could write that. We could rewrite this query, right? We could use a left, a derived left join, right?

Because a number sub queries in the select list will always be outer joins because they’re, they’re not filtering out any rows, any rows that qualify to be projected from the query. We want to find what the, the, whatever calculation in this, in the sub query.

So we could rewrite this as a derived left join. We could get the max for post type ID one, the max for post type ID two, and the total count. And we are looking for, of course, where post type ID is in one or two.

So we could do this and this would still be reasonably fast, but it is not 23 milliseconds fast. This is 59 milliseconds.

We spend 45 milliseconds seeking into the post table, and then another 14 milliseconds aggregating that data, right?

Because 59 minus 45 is 14. And this query ends up being just, just a little more than twice as slow as the three separate sub queries. We could even rewrite this as an outer apply, right?

With the exact same logic. Because remember, no cross apply because we’re not restricting rows. We’re just applying, we’re applying a calculation to the result, right?

We can run this, and this gets an identical plan to the derived left join, and ends up at 59 milliseconds, right?

We get the exact same query and timing from both of those. So in this case, making the three separate round trips is about twice as efficient as making a single, making a single trip to the table. Now, query rewrites to use specific syntax arrangements are not available in ORMs generally.

Many times we’re working with clients, we’ll stumble across really, really awful application generated queries.

I’ll, you know, we’ll look at them, I’ll be like, here’s a useful rewrite. Look how much faster this goes. We are twice as fast. We are three times as fast. We are 10, 100 times, 1,000 times as fast.

Look at how much better things could be if you weren’t using that ORM. Maybe, maybe we could take this, that, this query that the ORM is causing, is causing problems the way it generates, and maybe we could put that in a stored procedure with the way I’ve rewritten it, and make your life easier and better.

And they’re like, no, it’s ORMs only. And I say, okay, well, how, how would you use your ORM to build the query in this way? And they’re like, I don’t know, we can’t do that.

Okay, so, your only option is, is to stick with an ORM that builds an inefficient query, and you’re just, you’re just going to live with that, because you’re afraid of the stored procedures.

This is, this, this happens quite a bit, and like, I’m like, hmm. So, what, what, what should, what should we do here? What would you like me to tell you? What, what, how can I, how can I get this across to you in a different way?

Your ORM is good up to a certain point. These queries have reached the point where it is no longer good. You need a different API into the database, the stored procedure is just an API, right?

All it is is an access layer into the database. You can make this better. You can fix things. We can, we can improve things. All we have to do is get away from these ORMs. Not everything that, that, that, that, that, code produces is good.

Right? Built a query with code. The query sucks. Maybe, maybe the code sucks too. I don’t know. But, for the most part, people just don’t know how to improve that.

There’s really no, not a lot of fine grain control there. It’s up to you to rewrite queries with a better arrangement so that they can be faster. Now, in this case, both of the attempts at rewrites resulted in an identical query plan.

The optimizer did a fine job here, but both of the single trip queries were, but little bit over twice as slow than the original. In this case, the difference is absolutely microscopic, right?

It’s the difference between like 60 milliseconds and like 20 milliseconds, right? Not anything that we’re going to get crazy about, but I just do want you to see that like making those three round trips was more efficient than making the single round trip.

For me, the real advantage of writing out the three separate sub queries is to better understand which of those sub queries does work. You know, it’s almost the same thing.

And we’re going to talk about this more with CTE, but it’s very, very similar to that pattern where when I see lots of CTE being used in the single query, you know, one of the first things that I do is I start just individualizing those common table expressions as select into temp tables because it helps me figure out exactly which point in the CTE I can like has a, is like really slow or causing problems or causing bad estimations that trickle down to the rest of the CTE and where I can start fixing things.

And maybe, you know, sometimes the, sometimes the end result is that I need to use the temp tables. And sometimes the end result is that I can make meaningful changes within the CTE to make those faster. And we can stick with the original.

Now, if, like I was saying earlier, if these sub queries were a lot more complex, excuse me, and they had like a lot more going on in them, like, like we had to like find the top one, but then join to something else and have like an exist clause.

And, you know, we were just doing a whole lot more work in each of these. Then I would probably be a little bit more keen on like collapsing this stuff into one either derived join or apply, outer apply sub query or just like maybe dumping like, uh, where is it?

Sorry, up here a little bit. Maybe I would just dump this into a temp table with like an exists clause. Remember the exists in the last video with an exists on users so that I could filter out the size of this and, uh, you know, only bring rows in there and then just join the temp table to the, to the users table and bring that out.

Sometimes that can be a good approach too, but it all depends on, well, a lot of local factors. Again, a lot of this stuff does depend on local factors, but anyway, so sub queries in the select list, not always the worst choice.

Um, whenever someone says the row by row, well, it depends on what kind of join SQL Server implements to, uh, or rather what kind of physical join SQL Server implements.

to logically implement your sub query. If it’s nested loops, yes, it’s row by row, but you could get row by row from nested loops. If you write a join or an apply or anything else, all of these things can use nested loops and also be row by row.

So don’t let people drag you down with that because they’re idiots and they don’t know what they’re talking about. Be smart. Say, and say to them, I know what I’m doing.

Write the query. Show them it’s faster or show them it’s not slow. Show them it’s no different, whatever, but whatever, whatever you do, don’t let people get away with nonsense.

Don’t let people get away with their chat GPT responses to SQL, about SQL Server stuff. Cause it’s not always smart. It’s not always right.

I spend a lot of time with the AIs trying to get something good out of them. And it’s really hard. They’re abysmal places. Abysmal.

Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I hope that you will continue to write correlated subqueries. Because, gosh darn it, they can be pretty useful.

Alright. Cool. Well, I’m out of here. 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.