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