Filtered Index restrictions and Kendra Little’s Index Quiz
3July 20, 2015 by Kenneth Fisher
A few weeks ago I saw a tweet about Kendra Little’s (b/t) new SQL Server Quizzes. Of course I had to give them a shot and while I was somewhat disapointed in my scores on a few over all they were a lot of fun. I recommend giving them a shot.
In particular however there was one question that gave me pause. I felt like I should know the answer and I didn’t.
Let’s review the possibilities one at a time.
The last one is easy to disqualify.
The WHERE clause looks simple enough. In fact if this WHERE clause didn’t work then filtered indexes wouldn’t be of much use would they? In this particular case you can remove the WHERE clause and the index definition still doesn’t work. The most basic index creation syntax you can have is:
CREATE INDEX ix_name on TableName(FieldList)
And this one is missing the (FieldList) so it can’t possibly work.
So back to the top.
Now I know you can’t use functions in the field list, but in the WHERE clause? I’m pretty sure no, so let’s eliminate this one too.
The last two are a bit more difficult.
One uses an OR and the other a <> (not equals). I’ll tell you up front that I got this one wrong. I guessed, and any time I have a 50/50 shot, I almost always get it wrong. Logically though, (with some nice hindsight built in) it makes sense that the <> almost has to be allowed. Which means that the OR is not. And as it happens the more I think about it the more I feel like I remember reading that somewhere. So if we eliminate that one we are left with:
The moral of the story is that there is more to filtered indexes than just a WHERE clause. There are a number of restrictions and gotcha’s (at least one of which I’ll be writing about later) that you need to be aware of.
For some further reading on filtered indexes (and confirmation of the above logic) you can look in the following places:
- The WHERE heading of CREATE INDEX in BOL.
- The Brent Ozar Unlimited (b/t) blog What You Can (and Can’t) Do With Filtered Indexes
- Create Filtered Indexes
SQL Studies 
I’m not a big fan of testing people’s knowledge of syntax. Sometimes it’s really helpful to have syntax memorized – when the syntax closely aligns to an understanding of how the statement works – other times it seems sort of arbitrary. I’m guessing “OR” would be excluded here because the filtered index filter must be easy to translate into a range. A “” is probably easier than an “OR” but I’m just thinking “aloud” about this.
Do you happen to know why “OR” is excluded?
“I’m not a big fan of testing people’s knowledge” Well, yes and no. The point here and in my opinion ALWAYS the point (certs most definitely included) is to test yourself. To check your knowledge and see where you need some work 🙂 Exact knowledge of syntax certainly isn’t necessary but it can be a big help. The more you know the less you have to look up 🙂 As far as why no OR clause, I’d say you are probably right. I may take a look and see if things changed with 2016.
It’s true it takes time to look up syntax, but there is just way too much syntax for me to memorize. But yeah, you do need to push yourself sometimes.
I’m eager to see some of the changes in 2016. I watched part of a webcast yesterday about new features in 2016. I’m really excited about temporal tables.