Why is this filtered index not only not used but errors when I try to force it I'm trying to tune a rather annoying query where a number of the tables have something that looks like:

date = '9999-12-31 23:59:59.9999999'


At least a few of the tables are in the 300 mil row range and when I filter this I end up in the 2 mil range.  It seemed reasonable to attempt a filtered index.

CREATE TABLE test (col1 int PRIMARY KEY, col2 int, col3 varchar(50), col4 datetime2(7), col5 int);
CREATE INDEX ix_filtered ON test(col2,col3) INCLUDE (col4) 
       WHERE col4 = '9999-12-31 23:59:59.9999999' ;
GO

SELECT col2,col3 FROM test 
WHERE col4 = '9999-12-31 23:59:59.9999999';
GO


However when I checked the query plan it wouldn't use the index.  It just did a clustered index scan and a key lookup.  Obviously without any data this makes sense but even with all of my data it did the same thing.  The reason I haven't bothered supplying any data is this second problem that occurs regardless.

When I tried to force the index to see what the query plan would look like:

SELECT col2,col3 FROM test 
WITH (index (ix_filtered))
WHERE col4 = '9999-12-31 23:59:59.9999999'


I get this error:


  Msg 8622, Level 16, State 1, Line 52 Query processor could not produce
  a query plan because of the hints defined in this query. Resubmit the
  query without specifying any hints and without using SET FORCEPLAN.


I'm trying to figure out why I'm getting the error with the query hint.  My guess is that the answer to this will also tell me why the filtered index isn't being used at all.
We found that this error occurred because forced parameterization was on at the database level. For other readers  who want to reproduce it, use Kenneth's sample code in a database with this setting on:

create database filterme;
GO

alter database filterme set parameterization forced;
GO


For parameterized queries, SQL Server doesn't want to cache an execution plan with a filtered index that might not work for other parameter values.

In later versions of SQL Server, the query plan for the "non-hinted" plan would (hopefully) show an "unmatched indexes" warning to alert you better to this. That just didn't exist in SQL Server 2008R2. (Note: I just tested on SQL 2016 and am not seeing the unmatched indexes warning, need to look into more why it isn't showing up with this sample code.)

To get the query to work without changing the database setting, you can use an "Option recompile" hint in queries. That works because it instructs SQL Server not to cache the plan for re-use.