I know from other questions and posts that when SQL compiles a query plan it can only use a filtered index if the filtered index is guaranteed to be able to be used every time the query runs. This means you can't use a variable in the where clause because sometimes it might be able to use the filtered index and sometimes not.
One way around this is to use
OPTION(RECOMPILE), so that the times it can use it, it'll get the filtered index.
Doing some testing, I found that this query can use a filtered index (note, I'm forcing the index just to prove a point):
SELECT MAX(table1.SomeDateField) FROM dbo.table1 WITH(INDEX(MyFilteredIndex)) WHERE table1.filteredColumn = @variable OPTION (RECOMPILE)
However, if I want to assign the result to a variable, I'm bang out of luck:
SELECT @OutputVariable = MAX(table1.SomeDateField) FROM dbo.table1 WITH(INDEX(MyFilteredIndex)) WHERE table1.filteredColumn = @variable OPTION (RECOMPILE)
Msg 8622, Level 16, State 1, Line 15 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.
The query can clearly use the filtered index as it runs find when I don't want to save the output to a variable.
I have ways of rewriting this query to hard-code
@variable to remove the problem, but can someone explain why the first query can use the filtered index, but the 2nd query can't?