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.
Best Answer
I can reproduce this in a database with
PARAMETERIZATION FORCED
.The literal then gets parameterised and it is no longer guaranteed that the filtered index will match
In which case
OPTION (RECOMPILE)
allows the hint to succeed.