How To Write SQL Server Queries Correctly: Literally Everything You Need To Know About CTEs
Thanks for watching!
Video Summary
In this video, I dive into the world of Common Table Expressions (CTE) in SQL Server, providing a comprehensive yet critical analysis of their use and limitations. Erik Darling from Darling Data shares his extensive experience and expertise to show that while CTEs are useful for certain scenarios, they often come with significant drawbacks, such as redundant queries and performance issues due to single-threaded recursion. He emphasizes the importance of good formatting and comments over the supposed readability benefits of CTEs, encouraging viewers to focus on more effective query writing techniques like APPLY operators for simpler tasks.
Full Transcript
Erik Darling here with Darling Data, and in this video we’re going to continue on with our How to Write Queries Correctly series. And in this one I’m going to tell you, I’m going to teach you everything you need to know, literally everything you need to know about CTE for free. If anyone ever tries to convince you that they have something worth selling you, for you to learn about CTE, laugh in their face, throw something heavy at them. This is it. This is all you need to know about CTE.
Alright? So, before we do that, if you would like to spend $4 a month on this channel, you can. There’s a link in the video description where you can click that says, become a member, and then you become a member. And I get $3.10 a month after taxes. If you are just, no way, $4.00, you can like, you can comment, you can subscribe.
All very valid ways to show your undying love for me. Consulting! I do it. A lot of it. And according to my wife I need to do more of it, because it keeps me in my office. So, if you want to make my wife happy, you can hire me to do any one of these things, and more, with your SQL Server or servers.
And as always, my rates are reasonable. If you would like to get some very high quality, very low cost SQL Server training, none of it about CTE, at least, you know, not in a very elementary way, because I would never charge you for that. Save the good stuff for training.
You can get all of mine for $150 US dollars for the rest of your life. It’s not quite free, but if you live a long time, it’s close to it. There is also a link for that stuff in the video description.
You know, 2025, I’ll be on the road again for now. Give me a break. So, with that out of the way, let’s talk about the laughability of CTE. So, one, they’re just inline views, and you need them for some types of queries.
Unlike derived tables, you can reference them multiple times in a query. But this is where they start to cause problems. You can write modifications against them, which is mostly useful for doing ordered deletes.
For some reason, you can’t put a top with an order by and a delete. It’s very strange. Sorry, a delete with an order by and a top, a delete, and an order by. It’s weird. You can write recursive ones, but it’s really annoying.
And if you find yourself having to write recursive CTE a lot, you should probably consider a different data model. You can often do things a lot more cleanly with apply than you can with CTE. CTE require you to stack things one above the other and keep doing things.
With apply, you can just have a nice, neat query where you just have stuff very nice and streamlined and inlined without having multiple steps that someone has to read through and remember the column names and the CTE names. It’s all very aggravating.
So, like I said, the big gap that CTE were meant to fill in in SQL Server is with derived tables, you might notice some red squiggles here. That’s because we can’t talk to the derived expression x more than once.
Right? We just can’t do that. It just doesn’t work for us. Even if I name that correctly.
There we go. Even if I name that correctly, we still get red squiggles. And if I try to run this, it’ll say invalid object name. Next one, who are you? I don’t know.
But we can do that with CTE. The problem, as always, with CTE is every time you re-reference them, SQL Server runs the query in them again. If you look at this, we touch the user’s table not once, but twice.
And if we quote this in and we add a third join to our correctly named CTE, we will now see that we touch the user’s table three times. A one, a two, a three.
So, like I said, the thing that, like, Microsoft was like, cool, we have CTE now. Now you can reference them multiple times. No way to materialize them.
Other database engines give you that. Microsoft, as of this recording, does not. Maybe that’s a secret thing in SQL Server 2025. I don’t know. Couldn’t tell you.
I don’t know how much work goes into CTE. Microsoft is clearly busy with very important things involving fabric and AI that will go absolutely nowhere. So, we have that to look forward to.
Thanks. So, this is what I was talking about with deletes. You cannot do this, right? You can’t have that in there. But we can have that in here.
If we add an order by, u.id, this is completely valid where this is not, right? This will say, bleh, no. No ordering.
No ordering for u. This thing, though, this will give us a nice ordered delete, and we don’t have to worry about anything in there. There’s no sort in this because I’m ordering by the id column. I think when I was messing with this before, I think I deleted the order by by accident.
But if we were to order by a column that is not supported by an index, you would see it gets sorted there. When we use id, that is the clustered primary key of the users table. So, it’s already ordered for us.
So, we can delete things nice and orderly this way. Sometimes, you need a CTE. You could also do this with a derived table. My style guide says it doesn’t matter because for a query this simple, it’s just not that big of a deal, right?
You generate a row number, and you want to filter out to where that row number equals something. There’s no way in SQL Server to do that all in one step, right? You can’t do that all in one go for some reason.
And SQL Server, Microsoft makes you write a two-step query and generate everything. And then at the very end of that query, filter out everything in there, right? So, that’s a lot of fun, right?
Sometimes, you need to use them because Microsoft won’t implement useful things that make your life easier. You get big data clusters, synapse, managed instance. Why would anyone want these things?
They’re garbage. They’re stupid things. And, of course, you can write recursive CTE using CTE. Of course, you couldn’t write a recursive CTE without a CTE because there’s no just plain recursive.
You need that. But, you know, I did go a little bit more into performance details on this in the other video in this series about CTE, which you can find if you look at the playlist.
It’s all in there. But you start off with this part. And this is where you write the anchor portion of your CTE. And this is where you write the recursive portion of your CTE. And if you run this query, well, actually, let’s look at the query plan.
I forget. I don’t think I have indexes that make this good. So this would run for a long time. One of the big problems of CTE is that, recursive CTE, rather, is that the recursive part of the query is forced to run single-threaded.
And it can be very, very slow if you don’t have good indexes in place to support it. And another thing is that usually you’re going to see a lot of nested loops inside of your recursive CTE. And if you don’t have good indexes, you’re going to see eager index spools inside of here.
And that is a surefire sign that they are going to be terribly, terribly slow. So not a lot to say about the recursive CTE. Again, if this is something you need to do frequently, you probably need a better data model.
Now, for anyone who says they make queries more readable, again, for like the 10 billionth time, they don’t. They don’t make them more readable. They don’t make them more understandable.
What makes them, what makes queries readable is good formatting. And what makes queries understandable is good commenting. Writing a CTE does absolutely nothing to make a query either one of those things. It does not help.
There is still no explanation for what you did. There is still just wild formatting where you, for some reason, it’s like select and then like columns and then from is on the same line. And then join is on the same line, but on is down.
And like just format your queries and people will find them readable and make comments on your queries and people will find them understandable. It’s a fantastic thing. It’s a wonderful thing to do.
Otherwise, you’re just writing gobbledygook. So one thing that some people will do with CTE is stuff that they could do with apply very easily. Like, let’s say this is something that Itzik talks about sometimes in his training is that like just people who want to do simple things.
Like just get the year from a date or date time or whatever column. Date, date time to take time 1 million 17th. Yeah, fire.
Big city living folks, big city living. And then let’s say you wanted to get the following year, you could write another CTE. And then you could get all this stuff.
And then, I mean, granted, this is just sort of a goofy example, but it gets to the mentality of people who use CTE for just everything. Because they have it in their goofy heads that they think it’s going to make the query readable and understandable for everyone. Where you could just do something very simple like this and just cross apply some values to that year.
And then you have that expression available to you in the select list and your query becomes a lot more compact and you don’t need 17 different steps to express yourself or figure out or write your logic out. You can do things very easily and more compactly when you use better forms of query writing. So this is literally everything you need to know about CTE.
There is nothing magical or mystical about them. There is nothing fantastic about them. Perhaps someday materialization will come to SQL Server, but we don’t have that now. Fabric did just get Azure SQL database.
Wow. Hold your applause. No, I mean it. Hold your applause.
Anyway, thank you for watching. I hope you enjoyed yourselves. Honestly, I hope you learned nothing. Because this is all very generic information about CTE, but it is all the information you need about CTE. Alright.
I will see you in the next video about the output clause, which will be literally everything you need to know about the output clause. Alright. Have a great day. It’s pretty salty.
It’s clear!
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.