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