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.