Using a filtered index when setting a variable
The optimization that allows you to use the filtered index with a RECOMPILE
hint 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
There is one scenario where using
OPTION (RECOMPILE)
will not result in the parameter embedding optimization being applied. If the statement assigns to a variable, parameter values are not embedded:
This should work:
declare @myVariable int
declare @variable int = 9
declare @OutputVariable date
declare @t table(MaxDate date)
insert into @t(MaxDate)
SELECT MAX(table1.SomeDateField)
FROM dbo.table1 WITH(INDEX(MyFilteredIndex))
WHERE table1.filteredColumn = @variable
OPTION (RECOMPILE)
select @OutputVariable MaxDate from @t
And you can always use dynamic SQL with literal values instead of trying to get a parameter to transform into a literal with OPTION (RECOMPILE). eg
declare @variable int = 9
declare @OutputVariable date
declare @sql nvarchar(max) = concat(N'
SELECT @OutputVariable = MAX(table1.SomeDateField)
FROM dbo.table1 WITH(INDEX(MyFilteredIndex))
WHERE table1.filteredColumn = ', @variable)
exec sp_executesql @sql, N'@OutputVariable date out', @OutputVariable = @OutputVariable out
select @OutputVariable