Using “OR” and “IN” with SQL Server’s Filtered Indexes

You can’t do everything with filtered indexes in SQL Server. For instance, you can’t create the following index:

If you try, you’ll get the error message:

Instead, you can use ‘IN’ and create the index this way:

That works– and good news, even queries written with ‘OR’ can use that filtered index, because SQL Server is clever like that. Here’s an execution plan that shows it in action.

Index found!

The filtered index can also be used for a subset of the VoteTypeIds, too. Here’s an example where I use it just for VoteTypeId = 1. (Note that I had to force it to do it– it preferred a different plan.)

filtered index subset of in list

Takeaway: SQL Server’s filtered indexes can be complicated and limited sometimes, but don’t get too tripped up by the fact that they don’t allow “OR”.

Previous Post
“It’s Slow” Is Not A Metric
Next Post
What Problems can Extended Events Solve? (Video)

2 Comments. Leave new

  • “The filtered index can also be used for a subset of the VoteTypeIds, too. Here’s an example where I use it just for VoteTypeId = 1. (Note that I had to force it to do it– it preferred a different plan.)”

    Yes, it’s not preferred because the actual Vote Type ID’s (with only value 1) have to be looked up in the table. The values are not stored in the filtered index, it contains only values for Post ID for records with any Vote Type ID of 1 or 2.

  • Scott Chamberlain
    August 24, 2016 3:01 pm

    If I wanted to do

    WHERE (InstancesAllowed is null OR InstancesUsed < InstancesAllowed)

    Is that possible to re-write to make it work with a filtered index? I have been racking my brain but have not been able to figure out a way to translate it to a form that would work.

Menu
{"cart_token":"","hash":"","cart_data":""}