Filtered Index hint rejected by SQL Server

Question

I have a filtered non-clustered index that SQL Server is not using. I'm pretty sure the optimizer is doing the right choice but I would like to force it to run with that index so I can compare the plan and see why it's more expensive.

I've removed everything from the query and I'm just selecting the column that is being filtered.

Index definition:

CREATE INDEX idx_all
    ON tbl_test (CommentDateTime, name)
    INCLUDE (comment, CommentByType)
    WHERE CommentByType='INT';

The query I'm trying to run is:

SELECT CommentByType
FROM tbl_test WITH (INDEX (idx_all))
WHERE CommentByType='INT';

And SQL Server is returning the following error:

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've read a lot about the column filtered being included either in the key or as include column but nothing helps.

Answer 1

I can reproduce this in a database with PARAMETERIZATION FORCED.

CREATE DATABASE D1

ALTER DATABASE D1 SET PARAMETERIZATION FORCED

GO

USE D1

CREATE TABLE tbl_test
(
CommentDateTime DATETIME,
name VARCHAR(50),
comment VARCHAR(50),
CommentByType VARCHAR(10)
);

CREATE INDEX idx_all
    ON tbl_test (CommentDateTime, name)
    INCLUDE (comment, CommentByType)
    WHERE CommentByType='INT';

SELECT CommentByType
FROM tbl_test WITH (INDEX (idx_all))
WHERE CommentByType='INT'
--OPTION (RECOMPILE)
;

The literal then gets parameterised and it is no longer guaranteed that the filtered index will match

where CommentByType = @0

In which case OPTION (RECOMPILE) allows the hint to succeed.