Filtered Index restrictions and Kendra Little’s Index Quiz

3

July 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.

FilteredIndexRestrictions1

Let’s review the possibilities one at a time.

The last one is easy to disqualify.

FilteredIndexRestrictionsQ4

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.

FilteredIndexRestrictionsQ1

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.

FilteredIndexRestrictionsQ23

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:

FilteredIndexRestrictions2

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:

3 thoughts on “Filtered Index restrictions and Kendra Little’s Index Quiz

  1. Richard says:

    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.

      • Richard says:

        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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,614 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: