Aaron Bertrand has learned a thing or two about filtered indexes:
Confession time. For filtered indexes, I have long held the following impressions:
1. That the definition of the filter had to exactly match the predicate in the query.
2. That
col IN (1, 2)was not supported in a filter definition, since it’s the same ascol = 1 OR col = 2, which is definitely not supported.
If I were to take a wild guess, I’d think impression 1 was probably influenced by the extreme limitations filtered indexes have with parameterized queries. Anyhow, read the whole thing and learn why both of these are wrong.