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.
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.