You can’t do everything with filtered indexes in SQL Server. For instance, you can’t create the following index:
CREATE INDEX IX_Votes_filter ON dbo.Votes (PostId) WHERE (VoteTypeId = 1 OR VoteTypeId = 2); GO
If you try, you’ll get the error message:
Msg 156, Level 15, State 1, Line 5 Incorrect syntax near the keyword 'OR'.
Instead, you can use ‘IN’ and create the index this way:
CREATE INDEX IX_Votes_filter ON dbo.Votes (PostId) WHERE (VoteTypeId IN (1,2)); GO
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.)
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”.
Need to learn more about indexes?
- Read our most popular posts about indexes
- Check out our free tool to diagnose index insanity, sp_BlitzIndex®
- Buy our 6 hour training on How to Tune Indexes in SQL Server
- Join us in person for our 5 day Advanced Querying and Indexing training course