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

Filed under: Index, Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tagged: indexes, Kendra Little, microsoft sql server, T-SQL