Is Dropping a Temp Table in a Stored Procedure Beneficial?

Generally speaking, it’s best to put things away that aren’t being used. Don’t keep indexes that aren’t getting utilized because they are taking up disk space and still have to be kept up to date with changes. A table is still loaded up with old data that’s not being used but needs kept? Maybe it’s time for options like an archive database or partitioning.

While it’s not on the same level of importance, one related argument I’ve seen and been in is how to handle temp tables in stored procedures. Do you drop them at the end of a stored procedure or do you leave them to be cleaned up by SQL Server? Is one way better for performance than the other? Let’s do some testing and see what we find out.

Same But Different

Here’s our test stored procedure with no temp table drop. We’ll create a temp table and load it up with some test data:

CREATE PROC [dbo].[TestNoDrop]
AS
CREATE TABLE #Temp (
	ID INT IDENTITY(1, 1)
	,TEMP_VARCHAR VARCHAR(100)
	,TEMP_DATE DATETIME2
	)

DECLARE @RecordCount AS INT = 100;

WHILE @RecordCount > 0
BEGIN
	INSERT INTO #Temp
	VALUES (
		'Here is a test string of data!'
		,GETDATE()
		)

	SET @RecordCount -= 1;
END

For our second stored procedure, we’ll copy and paste but include DROP TABLE #Temp at the end:

CREATE PROC [dbo].[TestWithDrop]
AS
CREATE TABLE #Temp (
	ID INT IDENTITY(1, 1)
	,TEMP_VARCHAR VARCHAR(100)
	,TEMP_DATE DATETIME2
	)

DECLARE @RecordCount AS INT = 100;

WHILE @RecordCount > 0
BEGIN
	INSERT INTO #Temp
	VALUES (
		'Here is a test string of data!'
		,GETDATE()
		)

	SET @RecordCount -= 1;
END

DROP TABLE #Temp

Before we test each stored procedure, I’m going to run DBCC FREEPROCCACHE so that I can get a clear picture of results when running sp_BlitzCache later. Once proc cache is clear, we’ll start with TestWithDrop by running it 1000 times:

EXEC TestWithDrop;
GO 1000

Running TestWithDrop 1000 times on my machine took 7 seconds in SSMS. Do we see a difference without the drop?

EXEC TestNoDrop;
GO 1000

On my machine, this also took 7 seconds.

We can use sp_BlitzCache to take a closer look at how each procedure ran.

CPU is similar:

Duration and reads are close as well:

Nothing too surprising there after the times we had in SSMS. Based on what I experienced when running each procedure 1000 times and what is shown by sp_BlitzCache, there isn’t going to be a real noticeable difference by leaving the temp table or dropping the temp table.

What Should I Do Now?

It’s ok to explicitly include DROP TABLE or let SQL Server handle it for you each time the connection drops. If your stored procedures are already dropping temp tables, that’s great. If they’re not being dropped, I wouldn’t bother spending the time and effort just to drop them.

Thanks for reading!