Learn T-SQL With Erik: Indexed View Ups and Downs
Video Summary
In this video, I delve into the intricacies of indexed views in SQL Server, exploring both their advantages and limitations. I discuss how indexed views can significantly improve query performance when used correctly but highlight that they come with a set of constraints, such as restrictions on certain functions and clauses. I also cover practical examples and best practices for creating and maintaining indexed views, including the importance of schema binding, using count big in grouping elements, and understanding the implications of indexed view maintenance. Additionally, I provide insights into when SQL Server matches or fails to match queries with indexed views, emphasizing the role of hints like NOEXPAND and the necessity of statistics on these views for accurate query optimization.
Full Transcript
…things work better when you turn the microphone on. I’m Erik Darling with Darling Data, nothing if not professional recorders. Today we’re going to talk about more T-SQL learning, where we’re going to talk about indexed view ups and downs.
This is, of course, bite-sized portions, little morsel-sized portions of my full T-SQL course, which, if you go down into the video description, there is a link to buy at the pre-sale price. Well, the advanced material is polished up and tech-reviewed.
So, you can buy it for half price now or full price later. I can tell you which one I prefer, but… If we pit what I prefer versus what you prefer, you would probably end up just buying it now.
You can also do all sorts of other stuff with the links. You can hire me for consulting, become a contributing with money member of this channel, not with content. You can ask me office hours questions for free, right?
Ask me privately, I answer publicly, we all have a nice time. And if you don’t want to do any of that stuff with me, you can do the usual like, subscribe, tell a friend, yadda yadda yadda. Of course, I will be leaving the house a little bit, way more often than I usually do.
I’m going to Dallas, September 15th and 16th. I’m going to Utrecht, Netherlands, October 1st and 2nd. And of course, I’m going to Seattle, November 17th to 21st.
These all have something in common. And they are all pass related events. The two in Dallas and Netherlands are of course, Utrecht rather, are pass on tour events. Those are little mini events.
And of course, pass data community summit is the big one. At the big one in Seattle, I have two days of T-SQL pre-cons with Kendra Little, where we will light up the stage in your lives and your minds with T-SQL goodies. I lost my train of thought on that one.
Anyway, let’s talk about indexed views here a little bit. And let’s figure out the right SSMS spawn to open and let’s get into things. So teaching beginners about indexed views is a little bit difficult, mostly because once you’ve talked, once you’ve talked through all the limitations and once you’ve talked through all the sort of, you know, weird stuff between standard edition and enterprise edition, you’re left with people who are just completely blank eyed and slack jawed.
And it’s like, what is the point of any of this doesn’t make my life easy. How long has this feature been on this V1? Happened, right?
You got fabric. Oh, God. Regret. Anyway, so there’s some stuff that you can’t do in indexed views that has work arounds, right? Like you can’t use the average function directly, but you can do a sum divided by a count big.
All right. You can’t do a sum on a nullable column, but you can use is null on the nullable column to replace nulls with zeros. So you can get around that.
And you can’t do distinct, but you can group by, you know, any columns that you need to and have that get matched. So that’s all cool. Right? Great.
Here’s the stuff you can’t do. And to me, this is like a few of these are like table stakes, right? Like min and max. Like, how dare you? It’s like 99% of why I’d want a filtered or why I’d want an index view is min and max. Right? Aggregates.
How do we live? We can’t use window functions. So that includes anything with an over clause. We can’t use table expressions. That means common derived, anything like that.
We can’t use sub queries. This includes exists and not exists. Why you cannot use exists and not exists. I don’t know.
That’s a bit strange to me. You can do all the, I mean, you can do all the inner joins you want, but exists and not exists. Don’t work. Order by. I guess that makes sense. That would be a little weird.
Top and offset fetch. I also probably get that. No outer joins. That means left, right or full. You cannot use cross apply or outer apply. That is befuddling in ways, but okay.
No inline or multi-statement functions. I guess that makes sense. Inline actually probably doesn’t make a lot of sense, but whatever. No pivot, unpivot.
Having union, union all or accept or intercept. You know, I guess, I guess some of that, I mean, I’m not going to say it makes sense, but I will say, I could live without this stuff. Right.
The other stuff, some of this other stuff is like, we got fabric. Okay. Okay. So this list usually kills off most of the bright ideas people have. Especially once you take out min, max and any form of outer join, the use and utility of index views in SQL Server gets thrown directly out the window.
So then once you throw in the fact that all of your client options need to exactly match this magical incantation of things in order to match automatically with index views. The blank eyed and slack jawed developers. obrigadosellers.
Bears forget the functions… карт you to always view of that. Yeah.
Blackysle. Study does increase! Hey, well, you know what, you know what, I mean the limit it is essentially and send them to your exact object or just like this, my gosh that DM lid wascend to text. Let the survey just tell us that, you know. Even if I would remember this, you know what, I’d say to all ask us about these identifiers, but where are my mirasins?
Maybe you’d have to tell us our time I’m going to do that. So let’s create an index view that works, right? We’re going to create our alter this view. We got fabric.
Great. So now we have an index view. So stuff that you need to have in an index view, if you are going to, well, if you’re going to have an index view, it must be created with schema binding. That’s a pretty easy rule to follow.
And if you are going to have any sort of grouping elements in your index view, then you must have a count big in there. There are good reasons for this that the wording for always escapes me when I have to talk about it, but I’ll have dreams about it sometimes.
Now, because we have an index view properly, or rather we have a view set up with all of the required bibs and bobs necessary, we have done the incantations.
In order to index it, we are now free to do that. We can create a unique clustered index, and we can create a nonclustered index on our indexed view. And this all happens pretty quickly.
Now, a lot of people, when you’re talking about indexed views, will get, you know, worked up about indexed view maintenance. Ideal world, under, you know, most circumstances, you will be lucky enough that your indexed view maintenance will not be any more expensive than the maintenance of maybe an additional nonclustered index on the table.
This does change a little bit if you get any bright ideas about joining tables together in your indexed views, because then you start to get into stranger locking scenarios. But anyway, move on a little bit.
In some cases, a SQL Server can match some expression that you write to an indexed view, right? Let’s make sure actual execution plans are enabled here.
And if we run this, we will see that we do indeed do a seek into our indexed view, right? Look at us go. We have indexed view matched.
We did a good job there. We are using our post scores index view. That’s this thing. And we have hit the nonclustered index on total score in that index view. So good job, us.
100%. If we were to run this query and have it not work, one way that I can show you what it would look like, because, like, getting indexed views to not match is, like, I would have to, like, do something obviously stupid.
But, like, sometimes you might find yourself writing a query that you would expect to match the indexed view, and the query plan will end up looking like you just touched the base table anyway, which would be the case here.
Now, this happened because I used the option expand views thing. Little weird that that’s a thing, but okay, you know, we expanded our indexed view and got a worse query plan, right?
We had to do that whole calculation where we have that calculation basically stored in the indexed view. So, like, why would we want to expand that here? I don’t know, but we have the option.
I can show you that sometimes SQL Server will not adhere to our indexed view requests. And one thing that is important, and this is something that you will almost definitely need in standard edition, is the no expand hint.
So, just tell SQL Server you are not under any circumstances allowed to expand the indexed view, and we will indeed get usage of the indexed view here. So, we, you know, for a query that maybe SQL Server will be like, no, no, no, no, no, this would be too expensive.
We can, I’m going to expand this view and do the other thing. You can override SQL Server’s, you can override SQL Server’s thought process on that and say, no, thank you, no expand.
Please no. So, the no expand hint will work there. Now, but one thing that is important, whether you are on standard edition or enterprise edition, is that unless you use the no expand hint, SQL Server will not create or use statistics on your indexed views.
If you might get bad estimates, and you might even see missing statistics warnings if you don’t use it.
So, you know, at least maybe like, you know, when you start querying your index views, you might want to throw something on there with a no expand hint. At least get statistics created on that.
And then, you know, don’t forget to update your statistics because that’s usually a good thing, right? Usually. Stuff we can get away from. Stuff we can get away with sometimes.
Sometimes. But, anyway. We got fabric. Cool. So, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I will see you in tomorrow’s video where we have some equally interesting T-SQL stuff to talk about.
All right. Cool. Thank you and goodbye. Bye.
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.