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.