Should you DROP temp tables at the end of stored procedures?

There are two camps. One ends every stored procedure like this:

CREATE PROCEDURE dbo.do_work AS
BEGIN
    CREATE TABLE #work (id INT NOT NULL, pad VARCHAR(100) NOT NULL);
    INSERT INTO #work ...;
    -- ... use it ...
    DROP TABLE #work;   -- tidy people drop their tables
END

The other camp deletes that last line and says the server cleans up after you. Both camps are sure they’re right, and the argument usually ends with “it’s good practice” versus “it’s pointless” — no numbers.

So: numbers. Six cleanup strategies, benchmarked on SQL Server 2025, with row counts from 0 to 10,000 and one to eight temp tables per procedure — and, crucially, with temp-table caching verified per configuration by performance counters, not assumed. (Methodology — duration-based loops, interleaved passes, min-of-trials, baseline subtraction — is the same harness design as the variable-assignment benchmark; I won’t repeat it here.)

What actually happens when your proc ends

Two background facts make this question more interesting than it looks.

Deferred drop. When a procedure ends, its temp tables don’t get torn down on your session’s time. Cleanup is deferred and handled by a background system thread — your caller isn’t waiting on page deallocation either way.

Temp-table caching. Better yet: when a temp table is created inside a stored procedure (and a few conditions hold), SQL Server doesn’t destroy it at all. It truncates it, renames it to an internal hex name, and keeps it cached — one data page and one IAM page — so the next execution skips object creation entirely. Your “create” on the next call is really a rename of a cached husk.

Which immediately reframes the question. If the table isn’t really being destroyed, what is your DROP TABLE even doing — and can it hurt, by defeating the caching?

The six contenders

The harness generates one stored procedure per configuration — same body, different last lines:

Method	Cleanup	Temp table cacheable?
no_drop	none — let the server handle it	yes
drop	DROP TABLE #t;	yes (verified below)
drop_if_exists	DROP TABLE IF EXISTS #t;	yes
truncate_drop	TRUNCATE TABLE #t; DROP TABLE #t;	yes
no_drop_nocache	none	no — named constraint
drop_nocache	DROP TABLE #t;	no — named constraint

Why the last two? The documented ways to make a temp table non-cacheable include giving it a named constraint or running DDL against it after creation. The nocache pair is identical to the first pair except for one thing:

-- cacheable: unnamed constraint
CREATE TABLE #t (id INT NOT NULL CHECK (id >= 0), pad VARCHAR(100) NOT NULL);

-- NOT cacheable: the constraint has a name
CREATE TABLE #t (id INT NOT NULL CONSTRAINT ck_t_id CHECK (id >= 0),
                 pad VARCHAR(100) NOT NULL);

That isolates the two effects: what does the drop choice cost, and what does losing caching cost?

Trust, but verify: counting actual table creations

Claims about caching are usually folklore. They don’t have to be — the instance counter Temp Tables Creation Rate counts real temp-table creations, so the harness snapshots it around every timed loop:

SELECT @tt0 = cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name = N'Temp Tables Creation Rate';
-- ... timed loop: N thousand EXECs of the test proc ...
-- (delta / iterations) = temp tables ACTUALLY created per call

The result is unambiguous. Tables created per procedure call, measured across every configuration:

Method	Tables created per call
no_drop, drop, drop_if_exists, truncate_drop	0.000
no_drop_nocache, drop_nocache (1 table)	1.000
no_drop_nocache, drop_nocache (8 tables)	8.000

Two facts fall out before we even look at timings. First, caching really is doing all the work — across hundreds of thousands of calls, the cacheable procs created zero new temp tables. Second — and this kills a persistent myth — an explicit DROP TABLE does not break temp-table caching. The drop variants show exactly 0.000 creations per call. The drop is essentially a logical operation; the cached object survives it.

Result 1: the DROP isn’t free — it’s just cheap

Net cost per call (minimum across 8 interleaved passes, minus the empty-proc baseline of ~52 µs), one temp table:

Method	empty table	100 rows	10,000 rows
no_drop	268 µs	678 µs	4.86 ms
drop_if_exists	342 µs	741 µs	4.94 ms
drop	357 µs	767 µs	4.85 ms
truncate_drop	487 µs	1,030 µs	5.34 ms

I’ll be honest: I expected a flat line — I predicted in my notes that drop vs no-drop would be “no measurable difference.” The data said otherwise: not dropping is consistently the fastest option. The DROP TABLE statement costs roughly 60–90 µs per table per call on this box — not because anything is destroyed (we just proved nothing is), but because it’s one more statement your procedure executes, while the server-side cleanup path is cheaper than that statement. The effect scales with table count: at eight temp tables, the dropping proc pays ~0.5 ms more per call than the non-dropping one.

And TRUNCATE + DROP — the old “be gentle with big temp tables” folklore — is just two extra statements: reliably the worst cacheable strategy at every size, and at 10,000 rows it buys exactly nothing in return.

Keep the magnitudes in perspective, though: by 10,000 rows, drop and no_drop are a dead tie (4.85 vs 4.86 ms) because the insert dwarfs everything. The drop tax only shows up in procs that are called very frequently and do very little — which, to be fair, describes a lot of OLTP procedures.

Result 2: the thing that actually costs you — losing the cache

Same table, same rows, same code — one constraint got a name:

Method	empty table	100 rows	8 tables × 100 rows
no_drop (cached)	268 µs	678 µs	4.90 ms
no_drop_nocache	1,330 µs	1,910 µs	14.2 ms
drop_nocache	1,459 µs	2,007 µs	14.6 ms

Naming the constraint made the empty-table case five times slower — about 1.1–1.2 milliseconds of extra cost per table, per call, every call, forever. That’s real object creation and destruction in tempdb on every execution, and it scales linearly: eight non-cacheable tables cost ~9 ms more per call than eight cached ones.

Compare the levers. The drop-vs-no-drop choice — the thing teams argue about in code review — moves the needle by at most ~90 µs per table. Whether the temp table is cacheable moves it by ~1,200 µs per table: fifteen times more. The argument is about the wrong line of the procedure. Nobody reviews the CONSTRAINT ck_orders_status clause, and that’s the line with the cost in it.

What disables caching, per the documentation and confirmed by the counter here:

• named constraints on the temp table (name them only when you genuinely must);
• DDL after creation — CREATE INDEX, ALTER TABLE, … (DROP TABLE is the documented exception, and the counter confirms it). Since SQL Server 2014 you can declare indexes inline in CREATE TABLE, which keeps the table cacheable;
• creating the table in an ad-hoc batch / dynamic SQL instead of a module (procedure, function, trigger).

So what should you do?

• Stop adding DROP TABLE at the end of procedures for performance. It cannot help: the server’s cleanup is deferred off your session’s critical path, and the cached object outlives your DROP anyway. Measured, the DROP is a small per-call cost (an extra statement). If your team likes it for tidiness, fine — it’s cheap — but it’s a style choice, not an optimization, and in hot, lightweight procs the fastest version is the one with no cleanup code at all.
• Never TRUNCATE before DROP. Two statements, zero benefit, worst measured cacheable strategy at every size tested.
• Spend your review attention on cacheability instead: no named constraints on temp tables, no post-creation DDL (use inline index syntax), create temp tables in modules rather than dynamic SQL. That’s where the 15× lever is.
• DROP TABLE IF EXISTS #t at the top of a proc as defensive coding is the same story — one statement’s worth of cost, no caching harm. Keep it if it guards something real (nested procs reusing names), drop the habit if it’s cargo cult.

The honest caveats

Same disclaimers as the rest of this series: shared 4-core host, so absolutes are comparative — trust the ordering, the scaling, and the counter, not my microseconds. And this is a single-session measurement of per-call cost. The historical reason temp-table folklore exists at all — tempdb allocation-page and metadata contention — is a concurrency phenomenon, and caching matters there even more (cached tables skip most of the metadata churn that causes it). A multi-session throughput version of this benchmark is the natural sequel; if your workload hammers tempdb from hundreds of concurrent sessions, that’s the test that matters for you.

---

The harness — proc generator, duration-based timing loop, counter verification, and the full result set (31 configurations × 8 passes) — runs end-to-end against a disposable SQL Server 2025 container. Want the scripts, or want your own tempdb-heavy workload measured like this? Get in touch.