Press "Enter" to skip to content

Preconceived Notions with Filtered Indexes

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 as col = 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.