Temp Table Caching
Published: 25th November 2014
I had a discussion with someone the other day who stated that all temp tables should be dropped at the end of a procedure because otherwise they never get cleaned up.

I could understand the statement, but it’s not actually how SQL Server works because within a stored procedure temp table caching generally makes dropping redundant.

Basically SQL Server will do one of two things with temp tables within stored procedures… it will either drop them completely when the procedure stops running (the session ends) or it will simply truncate the table, rename it, and store the schema in the database ready to be re-used on the next execution.

The latter depends on whether SQL determines that the temp table can be cached, but as long as it does then the drop will achieve nothing at all, therefore rendering it completely redundant.

Running the following code will show that a real table will actually get physically dropped and re-created, therefore SQL Server has to generate a new object ID each time the real table is created.

if object_id('bob') is not null drop procedure bob
go

create procedure [dbo].[bob]
as
       create table
realTable
      
(
              
id int
      
)
      
      
select object_id('realTable')

      
drop table realTable
go

exec bob
exec bob
exec bob
go

if object_id('bob') is not null drop procedure bob
go


However in the following, logically identical, code we can see that SQL Server ignores the drop entirely, simply caching the temp table and then re-using that same one each time and hence the objectID remains constant…

if object_id('bob') is not null drop procedure bob
go

create procedure [dbo].[bob]
as
       create table
#temp
      
(
              
id int
      
)
      
      
select object_id('tempDB..#temp')

      
drop table #temp
go

exec bob
exec bob
exec bob
go

if object_id('bob') is not null drop procedure bob
go


Therefore in a stored procedure there’s not a compelling case to drop your tables… I find it’s better to allow SQL Server to deal with them itself.
Comments:
NB: Comments will only appear once they have been moderated.