You can’t do everything with filtered indexes in SQL Server. For instance, you can’t create the following index:
|
1 2 3 |
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:
|
1 2 |
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:
|
1 2 3 |
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”.
3 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.
i think you’d have to trial it out with a computed column that delivers the logic you need (not easy to say without the full use case at hand, or, much simpler, 2 separate filtered indexes : one ‘IA is null’ and one IU < IA.
probably….
that's what i'd try anyway