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.