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.

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

My goal is for you to understand your SQL Server’s behavior– and learn how to change it. When I’m not figuring out the solutions to your database problems, you’ll find me at user group meetings in Portland, Oregon. I also love to draw.

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