Drop or not drop temporary tables in stored procedures

Question

I saw this question quite a many times but I couldn't get the answer that would satisfy me. Basically what people and books say is "Although temporary tables are deleted when they go out of scope, you should explicitly delete them when they are no longer needed to reduce resource requirements on the server".

It is quite clear to me that when you are working in management studio and creating tables, then until you close your window or disconnect, you will use some resources for that table and it is logically that it is better to drop them.

But when you work with procedure then if you would like to cleanup tables most probably you will do that at the really end of it (I am not talking about the situation when you drop the table as soon as you really do not need that in the procedure). So the workflow is something like that :

When you drop in SP:

• Start of SP execution
• Doing some stuff
• Drop tables
• End of execution

And as far as I understand how can it possibly work when you do not drop:

• Start of SP execution
• Doing some stuff
• End of execution
• Drop tables

What's the difference here? I can only imagine that some resources are needed to identify the temporary tables. Any other thoughts?

UPDATE:

I ran simple test with 2 SP:

create procedure test  as
begin
create table #temp (a int)
insert into #temp values (1);
drop table #temp;
end

and another one without drop statements. I've enabled user statistics and ran the tests:

declare @i int = 0;
 while @i < 10000
 begin
 exec test;
 SET @i= @i + 1;
 end

That's what I've got (Trial 1-3 dropping table in SP, 4-6 do not dropping)

As the picture shows that all stats are the same or decreased a bit when I do not drop temporary table.

UPDATE2:

I ran this test 2nd time but now with 100k calls and also added SET NOCOUNT ON. These are the results:

As the 2nd run confirmed that if you do not drop the table in SP then you actually save some user time as this is done by some other internal process but outside of the user time.

Answer 1

You can read more about in in this Paul White's article: Temporary Tables in Stored Procedures

CREATE and DROP, Don’t

I will talk about this in much more detail in my next post, but the key point is that CREATE TABLE and DROP TABLE do not create and drop temporary tables in a stored procedure, if the temporary object can be cached. The temporary object is renamed to an internal form when DROP TABLE is executed, and renamed back to the same user-visible name when CREATE TABLE is encountered on the next execution. In addition, any statistics that were auto-created on the temporary table are also cached. This means that statistics from a previous execution remain when the procedure is next called.