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.