Filtered Index is a great way to improve your SQL performance and save a vast amount of server resources while processing your big data query.
Whenever you have a filtered index you just might find that it’s not used when its columns are compared to a variable. The thing is, SQL Server will always make an Execution Plan that would work with any values for those variable (including the values that don’t match the index’s filter).
Thus SQL Server will not use the filtered index.
There’s an easy way to fix that: adding the filtering clauses to your statement.
For instance, if you have an index on column ID filtered on ID ”, instead of using a clause like this: WHERE ID = @pID use a clause like this: WHERE ID = @pID AND ID ” SQL Server will then know it can use the filter.