Dropping temporary tables

Local temporary tables – tables with names that begin with a single # character – are dropped automatically by SQL Server when they are no longer in scope. So why drop them explicitly at all?

I'm writing this from the perspective of a data engineer. ETL processes or their components are often implemented as T-SQL stored procedures (SPs) which might move significant amounts of data around and take a while to run. I'm not talking about the sort of SP that needs to run in 30ms to guarantee application responsiveness!

Sometimes it's useful to store intermediate results in temporary tables, sometimes it's necessary¹⁾, and sometimes you find yourself maintaining code built by someone who just really loves them. Adding explicit DROPs to code like this can make development and maintenance a bit smoother.

Here's a stored procedure using a series of temporary tables – this one isn't doing very much, but it illustrates the pattern.

CREATE PROCEDURE dbo.MyEtlProcess
AS
 
-- First part
SELECT 
  [name]
INTO #tempTable1
FROM sys.objects
 
-- Second part
SELECT
  [name]
INTO #tempTable2
FROM #tempTable1
 
-- output final results
INSERT INTO dbo.Results (
  [name]
)
SELECT
  [name]
FROM #tempTable2

If a process like this fails, something I'm likely to have to do eventually is to pull up the SP code and run it – often I'll do that just by commenting out the ALTER PROCEDURE header. Suppose that the failure happens at the final INSERT, because there's something wrong with the contents of #tempTable2. Now I'm going to want to re-run the second part repeatedly, adjusting the code at each iteration to diagnose and fix the issue – so I'll need to DROP TABLE #tempTable2 several times.

A conditional drop already present in the code makes that easier and reduces the amount of change I introduce during diagnosis or repair.

Here's what that SP definition might look like. I'm using a mixture of conditional drop syntax – DROP … IF EXISTS needs SQL Server 2016 or later. Assuming dbo.Results already exists, I can run this code repeatedly without error.

--ALTER PROCEDURE dbo.MyEtlProcess
--AS
 
IF OBJECT_ID('tempdb..#tempTable1') IS NOT NULL
  DROP TABLE #tempTable1
 
-- First part
SELECT 
  [name]
INTO #tempTable1
FROM sys.objects
 
-- Second part
DROP TABLE IF EXISTS #tempTable2
 
SELECT
  [name]
INTO #tempTable2
FROM #tempTable1
 
-- output final results
INSERT INTO dbo.Results (
  [name]
)
SELECT
  [name]
FROM #tempTable2

The Drop-Create-Repeat pattern I described above is more than an approach to repairing code – it's likely to be the way I write it in the first place. The script I work with during initial development looks very similar.

Here I'm about to start writing the second part – so I'll want to create #tempTable2, take a look at it, probably drop and re-create it, repeat until it's correct. It makes sense for me to start with DROP … IF EXISTS for convenience during my initial work, but afterwards I can just leave it there for use in future revisions.

--CREATE PROCEDURE dbo.MyEtlProcess
--AS
 
DROP TABLE IF EXISTS #tempTable1
 
-- First part
SELECT 
  [name]
INTO #tempTable1
FROM sys.objects
 
-- Second part
DROP TABLE IF EXISTS #tempTable2

Dropping all temporary tables can be useful to smooth development as temporary table structures evolve.

Suppose I'm working on this code:

--CREATE PROCEDURE dbo.MyEtlProcess
--AS
 
-- First part
DROP TABLE IF EXISTS #tempTable1
 
SELECT 
  [name]
INTO #tempTable1
FROM sys.objects
 
-- Second part
DROP TABLE IF EXISTS #tempTable2
 
SELECT
  [name]
INTO #tempTable2
FROM #tempTable1

I run the code once, then decide I need the [type] field in #tempTable2, so revise the code like this:

--CREATE PROCEDURE dbo.MyEtlProcess
--AS
 
-- First part
DROP TABLE IF EXISTS #tempTable1
 
SELECT 
  [name]
, [type]  -- new column
INTO #tempTable1
FROM sys.objects
 
-- Second part
DROP TABLE IF EXISTS #tempTable2
 
SELECT
  [name]
, [type]  -- new column
INTO #tempTable2
FROM #tempTable1

My SSMS intellisense says everything is fine here – it recognises that by the time I create #tempTable2, #tempTable1 will have the necessary [type] field. But when I run the revised code, I get an error!

Msg 207, Level 16, State 1, Line 28
Invalid column name 'type'.

This is a binding error – the database engine is trying to bind the second part SELECT to the definition of #tempTable1 as it exists in my SQL session. To allow the code to run, I need to remove that definition by first dropping #tempTable1. That's trivial here, but in a large SP with many temporary tables, finding and running all your conditional drops can be tedious.

My solution is to drop all temporary tables at the start of my development script, like this:

DECLARE @sql NVARCHAR(MAX) = (
  SELECT STRING_AGG('DROP TABLE IF EXISTS ' + LEFT(CAST([name] AS NVARCHAR(MAX)), CHARINDEX('____', [name]) - 1), CHAR(13) + CHAR(10))
  FROM tempdb.sys.tables
  WHERE [name] LIKE '#%'
  AND CHARINDEX('____', [name]) > 0
)
EXEC(@sql)
 
GO
--CREATE PROCEDURE dbo.MyEtlProcess
--AS
 
-- First part
DROP TABLE IF EXISTS #tempTable1
 
SELECT 
  [name]
, [type]
INTO #tempTable1
FROM sys.objects
 
-- Second part
IF(OBJECT_ID('tempdb..#tempTable2')) IS NOT NULL
  DROP TABLE #tempTable2
 
SELECT
  [name]
, [type]  -- new column
INTO #tempTable2
FROM #tempTable1
 
-- Third part
DROP TABLE IF EXISTS #tempTable3
 
SELECT
  [name]
, [type]  -- new column
INTO #tempTable3
FROM #tempTable2

The snippet of dynamic SQL builds a conditional drop for every temporary table based on the list of tables in tempdb. This includes temporary tables which aren't mine, but that doesn't matter because – in the context of my local session – they don't exist. The GO batch separator ensures that all my session's temp tables are dropped before the development batch is sent to the database server – so no binding errors.

Dropping local temporary tables isn't necessary in deployed code because they get cleaned up automatically, but it can make development more convenient.

• DROP…IF EXISTS (or a pre-2016 equivalent) immediately before creating a temporary table makes iterative development smoother.
• Dropping all temporary tables at the start of a script in development protects you from binding errors.
• Allowing conditional drops to remain in deployed code makes future development easier – planned or unplanned!