Learn T-SQL With Erik: Expression SARGability
Video Summary
In this video, I delve into the concept of sargability and its limitations in table expressions like Common Table Expressions (CTEs) and derived tables. I explain that these constructs do not materialize their results, which is a common misconception among T-SQL developers. By examining execution plans and demonstrating with examples, I illustrate how SQL Server processes these queries without materializing the intermediate result sets, leading to potential performance issues. The video also covers the importance of using computed columns for indexing expressions within table expressions, as this allows for better query optimization and improved performance.
Full Transcript
Hey! It’s still me, Erik Darling, with Darling Data. You are still learning T-SQL with Erik. That’s me. This is, of course, the teaser material for my T-SQL course. It is currently $250. It will go up to $500 after the summer when the advanced content is done and recorded. All 23 hours of the beginner content is available, and you can start watching that now. And in this video, we are going to talk about sargability to a somewhat limited degree because there are many things to talk about with it, but of course, there’s only so much time we have when we’re doing a teaser. So, let’s get on with that. Where is SQL Server? I’m still not used to this new icon down here yet. That’s still very strange to me. I don’t quite know if it’s like some harnessing or a saddle or infinity or an hourglass. or a mobius strip. What are you going for with this logo? I don’t get it. It’s just weird blob. Anyway, the thing that I want to focus on in this video is that there is some delusion that remains amongst T-SQL queriers out there in the world that if you put some expression into a table expression, well, we’re going to use CTE here as a convenient vehicle for this example, but the same thing is true of derived tables and pretty much anything else that you just query all in one shot, that if you put some expression into one of these that you materialize that expression when you absolutely do not. There is no materialization of anything that you have to do all in one go.
So, let’s turn on execution plans up here. And we’re going to look at a query that says, select age underscore i. This is the CTE version, of course. You can tell because it starts with this foolish width. And it replaces all, remember the age column in the user’s table is all nulls. So, it replaces all the nulls with zero. And then we select a count out here where this column in this CTE equals zero. So, that is our where clause down here. Now, if we did this and we expected some materialization of the result, we might expect to see something in here like a spool or something else that noted that like a result, like something from our results was put into a temporary structure in the query.
But we do not have that. All we have is a clustered index scan and a stream aggregate which takes care of the count that we did. And if we hover over the clustered index scan, you will see that this predicate is applied where is null, yada, yada, yada. So, there is like this did not get materialized here. And it’s the same thing if we were to use a derived table, right?
So, if we say select count from and then in here we nest our expression and then we stick a where clause on it to say where this equals zero, we will see an identical query plan here. We’ll see where there is no like materialization of anything. There is a stream aggregate and there is a clustered index scan. And if we hover over this, we will see the same predicate applied here.
Now, this isn’t to say that you can’t, you can’t like fix this. It is to say though that table expressions cannot be indexed. You can put an index on the underlying table, but if you wrap your column in a function, you’re going to ruin how well that index can be used. So, like we don’t have an index here, which is whatever, because there’s not a point at creating one, right?
We wouldn’t be able to seek to anything in it anyway. If we put an index on the age column and we wrap that column in is null or coalesce or like left, right, replace, upper, lower, L trim, R trim, len, data len, any one of those things. SQL Server can no longer seek to rows or values in that index. So, don’t do that.
The way that you can tell if, like, you know, if there were like, if we were really going to go on board with like materializing result sets within derived tables or CTE, then it would be like an additional layer of being able to define indexes for that result set, right? Like that might be kind of fun or not. I mean, I think that would just be, that would be like an eager index fool, wouldn’t it?
I hate, we hate those things. But, you know, like Microsoft, we don’t have CTE or table expression, like derived table, table expression materialization. There’s not a hint for it.
There’s not a, at least as far as I know, there’s not a trace flag for it. So, we can’t do that. Like I said, we can put indexes on the underlying tables and the optimizer can pick those up and use them. But there’s no way to define an index on a table expression like this.
And likewise, there’s no way to materialize a table expression like this to like store the result of it somewhere. So, what you have to do if you want to fix something like this is you have to add a computed column that gives you that expression, which then would allow you to index that expression, right? And we can do this for free and we can do this for, well, not as free, but it is pretty easy for just a single column there.
And now, if we run either of these, we will be able to seek to those values because we created a computed column which mimics this expression and then we indexed that expression. But without that, we can’t do any of these things. And SQL Server does not lift a finger to help us one bit.
So, when you’re considering like, you know, query performance and things like that, a lot of the stuff that people will bury away into CTE to make their queries more readable or that they’ll, you know, like they’ll stick in there and think, oh, I have this magical like, you know, like result set that I can just refer to over and over again. It’s just absolute nonsense. So, don’t get stuck in that train of thought because you will end up writing some of the worst performing queries in your entire life.
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 I don’t, I forget. I think we might be getting into like modification queries next, which is exciting. It’s very exciting. Don’t discount the fun and excitement of modification queries.
After all, none of us would have up-to-date resumes if it were not for update, delete, truncate, drop table, all that other good stuff, right? Yeah. How else do you keep those things fresh?
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. 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.