curious_sqldba (4/11/2013)
ChrisM@Work (4/11/2013)
Lynn Pettis (4/10/2013)
I think I know why, the table #test are different tables when the stored procedure is run in different sessions. Therefore you will get separate plans for the execution of the stored procedure. If you run the second batch a second time in the same session it reuses the plan originally created.Edit: Actually the temporary table #test is different when created from different sessions.
A new plan for each new session, even. There's some useful info here.
Still doesn't answer my question. My temp is executed in block of where the condition is never used, but still there are multiple exec plans. Please go to the original post for more details.
I'm not quite sure what you mean. The article I referenced stated that for any chance of plan reuse, a temp table used by a stored procedure should be declared within it, not outside.
You may be missing a point about plan generation and conditional statements which is covered very well here[/url]: "The optimiser processes all branches of a conditional no matter which branch will be taken during execution, even if the branch can never be executed".
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden