• I agree that the 'lexical equivalence' statements are incorrect. This is a common misconception about filtered indexes. They would be pretty useless if where clauses had to be identical between filtered indexes and query filters.

    In order to get the filtered index used in wider filtering scenarios, where there is not a direct match with query filter and index filter, you will need to make sure that the column in the filter is either in the index key (making wider b-trees) or in the include (making fatter leaf level data, not so bad as in the key perhaps).

    The other gotcha mentioned regarding use of parameters and precompiled query plans (see post by SSC Veteran) is a real pain. You can do the rather painful (and not always feasible due to performance considerations) 'option (recompile)' on the query statement in question, or even force a stored proc recompile, with various messy implications. Using index hints in this scenario is only any use if you are sure that the parameter value will make the filtered index you are forcing a good idea (fine if all your parameter values fall into the filter condition but otherwise not really helping).

    I think the autoparameterisation issue above is something really lacking here. There should in my mind be some aspect of 'parameter sniffing' that takes this into account. I feel a 'Connect' submission is calling...

    The BOL entry for this subject is very misleading and does not really explain the point regarding what is required internally in the index for filters to really work. Is it my impression or has BOL gone downhill since 2008? The quality of some of the latest tech articles are really not cutting it.