A Little About Computed Columns, Filtered Indexes, and Indexed Views in SQL Server
Thanks for watching!
Video Summary
In this video, I delve into the world of indexed views in SQL Server and how they can be used to circumvent some of the limitations associated with computed columns and indexes. I share my experience from a previous attempt where my voice sounded like it had emerged from a crypt, which led me to create this video with a bit more preparation. We explore why you can’t create filtered indexes on computed columns or indexed views, and how we can work around these limitations by incorporating the filter expression directly into the view definition. I also discuss the practical implications for standard edition users and enterprise edition users, emphasizing the importance of using the `NOEXPAND` hint to ensure that SQL Server utilizes the index view effectively.
Full Transcript
Erik Darling here with Darling Data. And this is actually my second take of this video because on the first take, it was actually the first words that I had spoken aloud all day. And I sounded like I had just emerged from a crypt. And it was actually, it even felt strange for me. So in this video, we are going to talk about how you can use indexed views to get around some of the limitations with computing. columns and indexes in SQL Server. It’s going to be a lot of fun, assuming I don’t start sneezing at any moment now. So the big problem with computed columns is you can’t create filtered indexes on them. You just can’t do it. It’s impossible. You just get errors. It’s actually kind of a miserable experience. Like many other things in SQL Server, it is pure pain and agony and suffering. So here’s what we’re going to do. We’re going to create this table called indexed view, which is not confusing at all, is it? It’s sort of like when you have a table with TBL in it, and then eventually something happens where you have to convert that to a view, it hits like a table with a different definition. And now you have a view called TBL something. It’s the same deal, right? So that’s good times. And we’re going to stick a little bit of data into this table just to make it semi-realistic. It’s only about 15,000 rows. Not a lot. That’s okay. We don’t need a lot to sort of prove through the concept. Now, the problem, like I was saying, is that you cannot create a computed column on an indexed view.
I’m sorry, on a filtered index. So what it says here is, filtered index n cannot be created on table dbo.indexed view because the column, not fizzbuzz in the filter expression is a computed column. So helpful. Rewrite the filter expression so that it does not include this column. Well, it’s the only column that I care about filtering, so what should I do there, Microsoft? Tell me what I should do. Illuminate me. I would love to know more. The one thing that I want to do can’t be done. Why bother telling me to do something else?
So here comes the indexed view. And before I create this, I should pause for a moment because, you know, again, like most things in SQL Server, there are agony and pain and suffering. Indexed views and their limitations are certainly a large source of pain and agony and suffering for many SQL Server users. They can be used to great effect in very simple scenarios. And perhaps their limitations are a feature and not just pure, you know, unabashed laziness.
Because they do kind of prevent people from putting really crazy things into index views and maybe index view maintenance for those things would be tough. But it is a real shame that even basic things like min and max aren’t supported by index views. Legend and lore has it it’s because Connor Cunningham decided one day that he decided when they’re putting index views in that having an additional nonclustered index to support the min and max was just too much.
So we don’t get that now. Great. Connor is working on CPU instructions and we’re still suffering with not being able to put min and max into an index view.
So the happy time for us, us leftovers in SQL Server. Grand. So, yeah, we can’t create this. We get enough errors.
No matter how many times you click this, we get errors. Now, the main place that I still find enough valid use for indexed views is on standard edition. And they’re great for standard edition because Microsoft has hobbled columnstore in batch mode in standard edition to the point of utter disrespect for their users.
So, like, often it’s just like, we can try it. What’s the point? It’s just, it’s like nothing.
It’s pointless. So, indexed views can be good because, you know, where columnstore in enterprise edition excels at being able to do large aggregations very quickly, you can often use indexed views to pre-compute those aggregations.
Indexed views do, of course, have, you know, maintenance downsides. But, you know, even in enterprise edition workloads where there’s some, you know, something about the workload or something about the table or something about, I don’t know, a million other things that make using columnstore indexes impractical or impossible, indexed views can even still be good there.
But, you know, in this case, we’re using it to get around a rowstore limitation with filtered indexes. So, let us continue. We are going to create a view.
And I just want to show you this part first because this part is interesting, too. We’re going to create this view called computed column. Again, not at all confusing. And we’re going to attempt to create a computed column, attempt to create a filtered index on that.
Now, we’re going to get a completely different error here. We are going to, it’s going to say, you know, let’s put this, let’s format this a little bit for easy reading. All right?
We don’t want, we don’t want to make reading any harder than it is. We are, we are high school dropouts after all. So, the filtered index can’t be created on computed column because it is not a user table. It is an indexed view.
Indexed views in general cannot have filtered indexes on them. So, but at least this error message is somewhat helpful. Consider creating an indexed view with the filter expression incorporated in the view definition.
Boy, howdy. We can do that, can’t we? We can follow those instructions. Those are actually useful instructions.
Whoever wrote that error message, you deserve some kind of, some sort of gold medal. I hope that you have gotten a good job after that. So, we’re going to put our filter expression into the indexed view.
And we are going to quote this part of the indexed creation out. And we are going to recreate both indexes. Now, this gets us where we want to go.
We have essentially an indexed materialized pre-computed thing filtered to the stuff that we care about. Right? So, like that computed column.
And the big problem, of course, was that we can’t create a filtered index on a computed column. We can’t create the indexed view to materialize that. And, you know, under most circumstances, as long as you have reasonable supporting backing indexes between, you know, the tables and the indexed view and the indexed view.
And, you know, just being honest, I’m really not a big fan of indexed views that span multiple tables. I’d rather create, like, two, like, indexed views with, like, one table apiece and then them join two tables in an indexed view most of the time. And it’s a treacherous set of circumstances.
But now we have what we want there. And part of this is going to be because of the small data set that I’m using. We don’t get a very, you know, interesting, we don’t get an interesting enough query plan just selecting some data from the table or just getting a count from the table matched on the not fizzbuzz column being equal to zero.
Right? We just, it’s just, it’s not 15,000 rows. It’s just, like, in real life, if someone was, like, we need an indexed view on this 15,000 row table, I would probably punch them.
It’s not reasonable. So when we run this query and we look at the execution plan, we will see something that we’ve seen in a few other examples and videos that I’ve recorded here before. This query is at the mercy of both getting a trivial plan and simple parameterization.
We can tell because the literal value that I used before has been replaced with at one. And that we have all these silly brackets that are completely unnecessary injected into our code. And we have a complete lack of as in the aliasing, which is something that I would never do because I’m a professional human being.
So in order to get around that, you know, you can always do the old one equals select one trick. If you’re going to, if you’re thinking about typing in the comments, why are you using one equals select one? I have bad news for you.
Type it into a search engine instead, and you will get both a blog post and a video where I explain it. So if you ask in the comments, you’re out banning you from my channel for life. Kidding.
I’m not. I will probably make fun of you a little bit, though. So if I run this and I look at the execution plan. Oh, yeah. Well, actually, I should backtrack a little bit.
What I meant to say up here is that because of all that, you can notice that instead of using the index view that I created, we’re using the base table, which is named index view. So that’s the thing we don’t like there. But if we add in the one equals select one, we avoid the trivial plan and the simple parameterization, we switch to using the index view that I created called computed column, which is precisely what we wanted.
But an important thing, particularly for standard edition users, the much abused, left behind standard edition users, is that you usually want to include the no expand hint. Standard edition does nothing for like computed column or rather does nothing for like index view matching. The no expand hint is necessary in like 99 point nearly infinite nine cases where I need where I want.
I need to I need to make sure that the index view is routinely hit rather than the base table base table behind the index view. The other really important thing, even for enterprise edition users, when it comes to index views and using the no expand hint, is this is the only way for SQL Server to create system statistics on columns in the index view. If you don’t put no expand in there, you don’t get any like it doesn’t create histograms for you.
It’s really weird. I don’t get it personally, but it is it is it does appear to be the case. So sometimes in query plans, you might see a warning that says like like, oh, columns with no columns with no statistics or something like that.
It’s a very misleading warning. Sometimes it’ll happen because it’s a index view and you don’t have statistics because of it. You didn’t use no expand when you queried it.
Other times it’s because you don’t have statistics in the very specific column order that SQL Server would want. And so like you do have statistics on these columns. It’s just not the exact it’s it’s almost like a like like a missing index request that’s wrong.
That’s like a missing statistics request that’s wrong because you do have statistics. It’s just not the specific statistics that SQL Server wants. So what did we learn today?
What did what did what did we learn? SQL Server has a lot of bizarre limitations. You know, you can create computed columns. You can create filtered indexes.
You just can’t create filtered indexes on computed columns. You can create index views, but you can’t create filtered indexes on index views. You can apply filters to index views and then create whatever indexes you want on that index on that view clustered and then whatever nonclustered indexes. But index views are a tough or a tough sell in a lot of cases.
You know, with the exception of people on standard edition who are have who, you know, can’t have like a just beat to death version of columnstore and batch mode available. You know, that’s that’s no good. And then like index views can be good there.
And then if you’re on enterprise edition, but for some reason column stores and column stores and no go for you for whatever other, you know, weird pathological reasons you have in your database, whether it’s, you know, the data types, cursors, you know, other kind of constraints and stuff. That’s another, you know, another story. columnstore does have some limitations, but, you know.
So I am still rather fond of columnstore most of the time. Rather, rather, rather sunny about columnstore. Got some good stuff going for it.
And Microsoft actually actively seems to be working on columnstore, which is a nice change of pace from index views, filtered indexes, partitioning, you know, a billion and a half other features that have been left at sort of like V1, V2 with no real investment afterwards. So, you know, columnstore at least has that going for it. It’s okay there.
So, there we go. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. If you like this sort of SQL Server content, then you should, you can like the video. You can comment on the video.
You can subscribe to my channel, and you can join over 4,000 other lovely data darlings and getting notified every time. I publish one of these videos so that you can stay on the, I don’t know. I’d call it the cutting edge, but, gosh, is SQL Server the cutting edge anymore?
I often wonder. I often wonder. It’s a fun thing. Fun thing to ponder.
Fun thing to consider. Why? What happened to SQL Server? Why is everything seemingly spinning out of control? So, you can do that.
And then, I don’t know. Maybe you just like looking at me. Maybe you just like the sound of my voice. I don’t know. I don’t know what it is that gets people to subscribe.
But, if you do that, you get notified. And I’ll get larger subscriber counts. And larger subscriber counts are kind of my fetish at this point. So, you know, the higher that number goes, the happier I am.
And the happier I am, the more I record. So, it’s like a good feedback mechanism. You subscribe. I record.
We’re good. Right? Everyone. It’s a happy ending for everyone. All right. Apparently, I have a call starting soon. So, I’m going to go do that. And that call is with a nice client who is paying me money so that I can record these things for free.
There aren’t even commercials on my channel. Like some other SQL Server channels. So, you can watch these things uninterrupted.
And if you would like to hire me for consulting, well, I mean, you know my name. You know that my name is my website. And you can always get in touch with me that way.
All right. Great. Cool. Now, we’ve done all our plugs at the end. Because I’m an idiot and I should do it at the beginning. But, you know. Can’t teach an old dog new dogs.
All right. Goodbye. 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.