Esat Erkec takes us through a frustratingly near-useful feature in SQL Server:
Indexes are the special data structures that help to improve the performance of the queries in SQL Server. Against this great benefit of the indexes, they occupy space on hard drives and can slow down the data modification operations (update, insert, delete) performance. When any query modifies the data in a table the database engine needs to update all of the related indexes where data has changed. In certain instances, to minimize these disadvantages of indexes, using SQL Server filtered indexes might be the appropriate approach. Assume that, we frequently query a small subset of a table with the same conditions and the rest of the table contains too many rows. In this scenario, we can use a SQL Server filtered index to access this small data set faster so that we can reduce storage and index maintenance costs.
When you can get them to work, such as when the part of your query which is supposed to access that index is not parameterized, filtered indexes are great. But Esat does get into what happens when that condition doesn’t quite hold.