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.