j.miner (4/8/2013)
2 - The plan with the variable will be recompiled every time. This is due to the fact it might change.This is even more likely if the input parameter is set to a local variable before executing. And the local variable is used in the where clause.
Not true.
Variables do not cause recompilation (nor do constants or parameters). The plan will be cached and reused just as any other query, in fact, the caching will be exactly the same as the version of the query with the constant in it.
What causes recompilation:
The OPTION (RECOMPILE) hint (actually causes compile, not recompile)
The WITH RECOMPILE procedure option (actually causes compile, not recompile)
A change in statistics
A modification to an object that the query is dependent on.
Rebuilding an index on a table the query uses.
Clearing the plan cache (actually causes compile, not recompile)
See http://sqlinthewild.co.za/index.php/2011/08/16/compiles-and-recompiles/ for what causes compiles and recompiles.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability