Learn T-SQL With Erik: Common Table Expression Mediocrity
Video Summary
In this video, I delve into the world of Common Table Expressions (CTEs) in SQL Server, often highlighting their perceived benefits and the reality of their implementation. I start by addressing a common misconception—that CTEs make queries more readable. Through examples, I demonstrate how what might seem like a well-structured query can actually be a disaster, leading to errors when executed. The video then transitions into an in-depth look at SQL Server’s handling of CTEs, emphasizing the lack of materialization and the potential for performance issues due to repeated execution. I provide practical advice on when and how to use CTEs effectively, suggesting that in many cases, dumping their results into temporary tables can significantly improve query performance. Throughout the video, I share my frustration with SQL Server’s limitations and the workaround nature of using CTEs, ultimately aiming to help viewers navigate these challenges more confidently.
Full Transcript
Erik Darling here with Darling Data. In today’s video we’re going to get back to the T-SQL learning material. This is of course the beginner stuff, so if you fancy yourself far beyond the beginner realm of learning in this area, you can feel free to go twiddle your thumbs elsewhere. But we’re going to talk about CTE today. And this is one of my favorite things to talk about. because I like watching the balloons deflate when we start talking about just how mediocre SQL Server’s implementation of CTE is. You know, instead of getting some basic useful database functionality, we get giant monolithic failures like fabric foisted upon us and I don’t know, I guess let the layoffs continue. All right, good job all around. So one of the first things that every LLM generated idiot on the internet likes to say about CTE is how they make queries more readable. Oh, they’re so readable. Look how readable the query is with the CTE. My goodness. Let’s finger point, rocket ship, green check, fire emoji our way to fame and fortune. One wonders if these people have a lot of an assistant to remind them to have an assistant to remind them to breathe. But who knows. So here’s a query that is a CTE. We can identify quite easily that it is a CTE because it starts with with. It does that. It doesn’t need a semicolon, does it? But this query is completely illegible. There is nothing readable, understandable, or even tolerable about this query. It is a disaster. If someone sent this query to me, I would throw them out a window. If you don’t have respect for me, that’s one thing, but at least have some self-respect when you write these things. Even better, is if we attempt to run this query, we will get an error.
And, you know, the error is very clear. But where we would go about remedying this error is not terribly clear based on this syntax, is it? So please format your queries and they will be readable. If one does not format their queries, they will not be readable no matter how many CTE one dispenses with. So with that out of the way, let’s talk about SQL Server. So with that out of the way, let’s talk about SQL Server’s utter mediocrity with CTE. Now, the big thing is that even though the word table is in the name, the result of your query is only tabular. It is not materialized to a table. Nothing, there is nothing stable about the result of your query. There are all sorts of things that can come up, especially when one starts pondering isolation levels and the timing of operations in a query plan that might, they could set one’s head on fire if one spent too long.
So it’s quite a dizzying array of issues that you could run into. But that’s getting a little bit in front of things. That’s getting a little bit in front of things. The main thing is that the query is not materialized. Even if you put a top in there or something, which does provide some like logical fencing of stuff. If you watch the unnesting video that I recorded a few days ago, you’ll see that I use top in there to prevent some unnesting. In a similar way, you could do that with a CTE.
However, that result is still not materialized. And what I mean by that is that every time you reference the CTE, generally in the outer scope of the query, and by outer scope, I mean after the CTE has been run, every time you do that you have to run the query inside of the CTE. An easy way to see what I mean by that is by just getting an estimated plan for this one, where there’s one reference to the CTE, and there’s only one time in the query plan when we touch the users table, and where we generate some query plan operators to create our row number.
We’re calling the row number function here. And there’s only one filter to remove any rows where row number is not between 1 and 100. We have to contrast that a little bit with a slightly different query, where our from clause now joins the CTE, c1 to itself. They, of course, have different aliases because, you know, you can’t alias the same thing the same way twice. You just get an error and say, hey, you already did that.
No need to do that again. So if we now get the estimated plan for this query, we will see that we have now two copies of the CTE being executed, or being referenced, right, where the query gets executed. We touch the user’s table twice. We do all the stuff that we need to generate the row number twice in each of these query plans, right?
We have a lot of things that repeat in here, and we have two filter expressions, one for each time that we filter on the row numbers down here in the where clause outside of the CTE. And this is a very general pattern that you will see over and over again if you are the type of person who uses CTE and then re-references that CTE multiple times in the resulting query. So be very careful with this. If you’re going to do this sort of thing, I mean, you know, if the thing in your CTE is small and compact and easy enough to run, you might never have a problem with it.
But if you find your queries that exhibit this pattern slowing down considerably, strongly consider dumping the result of your CTE into a pound sign or hash sign temp table, and then using that temp table in your outer query instead. So that’s what it did. Now, it’s not just the re-referencing the lack of materialization that this can cause an issue with.
Quite often, even if you have every CTE, like if you stack CTE together, you have like a whole chain of them, and you have a different query in each one. And then in the outer query, you only talk to each CTE once, but you like join the results together in some way, either with a traditional join or like an in sub query or not in or exists or not exists or anything like that.
Cardinality estimation gets very, very difficult when you start combining all those things together. The reason for that is, you know, cardinality estimation can be difficult enough. If you think about a query plan, if you read it from left to right, you of course sort of get the logical flow of the query and like, you know, how things like got to where they needed to get to.
But if you start at the outer edge of the query, by that I mean like the stuff that’s kind of behind my big head, like these things over here. This is like the outer edge of the query plan. And like, you know, cardinality estimation can be tough there if you have a rather complex set of predicates against the table or just confusing like weird or stuff going on over and over again. This is like the outer edge of the query plan here. So like cardinality estimation here might be okay.
But as you start moving across the plan, when you start attempting to join complex expressions together, like as you get like, like deeper in like, or rather like, like further to the left in your query plans. That’s where cardinality estimation generally tends to fall apart. And that’s where materializing results into temp tables can be very valuable. Because now SQL Server, like even if it messes up cardinality estimation completely in the query that populates the temp table, once that result is materialized, SQL Server has every opportunity to better cardinality estimation with that, like physically materialized result.
So like, like, like, like when you see query plans, and like SQL Server’s optimized result cost-based, right? Figures out like, like along the way, it figures out all these different plan shapes and candidate plans and, you know, substitutes different operators to do different things and reorders joins and all sorts of crazy mathy stuff. But like, like, like, like, like, SQL Server is trying out these candidate plans, you’re going to see a lot of like weird Franken plan mix and match stuff where it’s like, oh, this is cheap.
Oh, now put in this cheap part. Okay, now put in this other cheap part. So like cardinality estimation can get really, really wonky and big, complicated plans, because all of a sudden, they’re sort of like these stitched together cost based choices. And things can really start misaligning. So like, it’s not just the lack of materialization with the CTE that can be painful, even if you don’t like, even if you don’t re-reference a CTE, string together a whole bunch of complicated ones, that can, that can also just make life weird. So like, like, I tend to avoid that as much as possible. A rather uncomplicated example, and this is not like a bad cardinality estimation example, this is just to like show you that sometimes like not, you don’t always, the lack of materialization doesn’t always come back to hurt you with the query being rerun.
This is just a simple stacked CTE where, you know, we have C1 here, and we run the query in here. And then down below it, we have C2. And we, when we reference, well, we definitely reference C1 here, and then select from C2 in the outer part. But what’s nice, like, C2 doesn’t mess this up. This doesn’t end up, this doesn’t, like, stacking CTE doesn’t result in the user’s table being hit twice, or the query inside the initial CTE getting executed twice. We only have that once in the stacked sort of thing there, the stacked CTE list here, where things, and actually, something that I think is kind of nice about this one, is you’ll notice that, like, in here, we generate our row number, right? And down here, we filter on that row number between one and 1000. But then in the outermost query, the outermost scope, we filter to where the row number is between 200 and 500, right? Which is a narrower sort of narrower set of, narrower range of values than 100 and 1000.
SQL Server only chooses to filter once, like, we don’t have an intermediate filter, and then a secondary filter, SQL Server just does one filter to where it’s between 200 and 500. So the optimizer does some work and just kind of like throws this portion out, it just says, we don’t actually need you to do anything, because like, you’re not really, like, there’s like no benefit to this. If we did something where, like, this was between 1 and 50, and this was between, like, 10 and 30, then it would filter twice, or like, then it would just filter out here.
So where CTE generally become useful is when you do things that are disallowed by T-SQL, like, just, like, on their own in a single query. One of the, probably the most, like, useful common example would be, like, deleting a top number of rows in an ordered way, right? So if we wanted to delete from this table, based on this where clause, and we wanted to order it by something, notice that we have a little red squiggle here, right?
SQL Server is like, like, IntelliSense is already telling us, hmm, I don’t know about this one. I don’t think that’s going to fly. And if we try to get an estimated plan for this, it would just say incorrect syntax near the keyword order.
Right? It doesn’t tell you, like, hey, you can’t do that. Like, you’re just going to sit there and stare at this query and be like, how, where, there’s no, there’s nothing wrong. If I run this part, I get a query plan.
But if I try to order by here, I don’t get a query plan. Why? There’s no, there’s nothing wrong here. Is it like you’ll start, like, putting this in, like, notepad++ and looking for, like, strange empty space characters and losing your mind. It’s just a T-SQL limitation.
But you can do that with a CTE where if you put a select top 1000 query in the CTE with your order by, you can delete from that and get a query plan just fine. Right? So this works, but just doing it in one query doesn’t.
So a lot of the utility and use of CTE is not performance. It’s not readability. And it’s certainly not, like, some sign that you know what you’re doing with T-SQL if you use them.
It’s how you use them. Right? Like, really, where they come in handy is where you do things that you can’t just do in one simple query. It’s like, you know, coming back to the row number stuff, you have to put that row number in some sort of derived table expression, whether it’s a CTE, whether it’s a derived table, you know, like, anything like that, in order to filter on the row number.
Other databases have a qualify clause that allow you, that allow, it’s sort of like a secondary where clause that allows you to filter on stuff that happens in the select list. Remember, we talked about logical query processing. Select happens almost last when queries are logically processed.
So stuff that you talk about in the select list isn’t visible to the where clause. If we had the qualify clause, it would be visible there. But we don’t.
Instead, we have fabric, fall down fabric, which, you know, just complete waste of our lives. So, like, most of the use of CTE just comes down to getting, like, T-SQL, there’s a workaround. Right?
Like, it’s never like, hey, there’s a straightforward way to do this. It’s always like, there’s this weird hack I read about. Right? It’s like, it’s never, almost never just like, oh, yeah, just do this one simple thing. It’s always like, no, no, no. You have to do this, like, four other things to get it to work, but it’ll work.
So, like, it’s really just a T-SQL limitation where we have to generate the row number in here before we can filter on it anywhere else. Or before we, like, you know, we can order by it up there, but we couldn’t filter on it in there. Because order by happens after select, but where happens way before select.
So, like, would it be nice if we had the qualify clause? Yes. Would it save us a lot of weird time and, like, typing and all the other stuff? Yes.
But, hey, it’s more important that everyone has a non-functional data late or something. Right? Okay. Anyway, thanks for watching. I hope you enjoyed yourselves. I hope you learned something.
And I will see you in another video where we will probably talk about something else T-SQL because that seems like a reasonable thing to do. Of course, the pre-sale on this course, you can buy this course at the pre-sale price, 250 bucks, down in the video description there. This is all companion material to the T-SQL seminars that Kendra Little and I will be teaching at Pass Data Community Summit in Seattle this November.
If you are attending those, you will get access to this companion material as part of your admission to the pre-cons. Otherwise, you will have to buy it from me. And if you wait too long, it won’t be the pre-sale price anymore.
It will be 500 bucks and you will say, can I still get the pre-sale price? And I will say, no. Why didn’t you buy it in the months that you had to buy it for the pre-sale price?
Ding dong. Anyway, I am going to go do something else now. CTE have once again found a way to depress me.
Anyway, thank you for watching.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 25% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.