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)
results in:
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?
Best Answer
The optimization that allows you to use the filtered index with a
RECOMPILEhint is called the "parameter embedding optimization." That's a process where the query parser replaces the variable reference with the literal value inside the variable.See this post from Paul White for the reason why it doesn't work in your second case: Parameter Sniffing, Embedding, and the RECOMPILE Options