Menu
If I have Temp Tables being created in a stored procedure's definition and then dropping them when I am done with them will it result in recompilation of execution plan?
For stored procedures every time its called? Any personal Experience? Any explanation please?
As when the temp tables are dropped at the end of every call, the execution plan becomes invalid. Does SQL Server still keep hold of the execution plan and reuse on next call or does it recompile it every time its called.
403
Stored procedures can reference temporary tables that are created during the current session. Within a stored procedure, you cannot create a temporary table, drop it, and then create a new temporary table with the same name.
If you are wondering why it is not required to drop the temp table at the end of the stored procedure, well, it is because when the stored procedure completes execution, it automatically drops the temp table when the connection/session is dropped which was executing it. Well, that's it.
The key advantage of using temporary tables is that they are visible only to the current session and they are dropped automatically when the session is closed.
A Local Temp Table is available only for the session that has created it. It is automatically dropped (deleted) when the connection that has created it, is closed.
Dropping of a temporary table doesn't matter. If a table is created (either permanent or temporary), all statement after that statement are recompiled (even if they don’t refer to the table). Calls to executable objects using EXEC aren’t recompiled. That's because SQL Server can create the plan after the objects are created. (In this case, the temp. table.)
You can monitor recompilation using Extended Events and its sql_statement_recompile or SQL Trace / SQL Server Profiler SQL:StmtRecompile.
Not the whole procedure is recompiled but only individual statements.
155
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
