Can a residual predicate be turned into a seek at run time
Seek Keys: Prefix: [UnvPclID].CntyCD = [#CntyCd].[CntyCd]
With a residual predicate:
[@FakeVarRealValueIs_0]=(1) OR [Edition] = [tempdb].[dbo].[#CntyCd].[Edition]
The table has a clustered index on (CntyCd, Edition, …).
The query uses OPTION (RECOMPILE) so I would expect the value of the variable to be sniffed, the OR removed, and the Edition predicate to be used in a seek, not as a residual predicate. Why is this optimization not performed in this case?
up.CntyCd = ds.CntyCd AND (0 = 1 OR up.Edition = ds.Edition)
This simplifies down to:
up.CntyCd = ds.CntyCd AND up.Edition = ds.Edition
That is now fully seekable using an index on (CntyCD, Edition…)
The reason it doesn't work in this query is a side-effect of assigning results to variables that means the value of the variable is not sniffed even though RECOMPILE is used. Without the variable sniffing, the expression cannot be simplified, so the seek remains on CntyCD alone, with the expensive residual including the variable test.
The seek will work as you expect with RECOMPILE if you do not assign the results to variables.
The red text above is a link to a Connect item where Microsoft confirm this behaviour with RECOMPILE.
Note that a pre-execution ('estimated') plan will not show any optimizations only possible at runtime due to the use of RECOMPILE.