Temporary Tables

Definition

A temporary table is a table whose lifetime and visibility are scoped to a session or a transaction rather than persisting like a normal table. It's used for staging intermediate results in complex, multi-step queries or ETL logic, and is automatically dropped when its scope ends.

sql
-- PostgreSQL
CREATE TEMPORARY TABLE temp_west_sales AS
SELECT * FROM sales WHERE region = 'West';

CREATE TEMP TABLE temp_calc (id INT, total NUMERIC) ON COMMIT DROP;   -- dropped at end of transaction

-- MySQL
CREATE TEMPORARY TABLE temp_west_sales AS
SELECT * FROM sales WHERE region = 'West';   -- session-scoped, dropped on disconnect

-- SQL Server
CREATE TABLE #temp_west_sales (id INT, region VARCHAR(50), amount DECIMAL(10,2));   -- local temp, this session only
CREATE TABLE ##global_temp (id INT);                                                -- global temp, visible cross-session until all referencing sessions close

How It Works

Most engines store temporary tables in a special namespace (e.g., pg_temp_* schemas in PostgreSQL, tempdb in SQL Server) that's automatically isolated per session, so two different sessions can each create a table named temp_calc without conflict. Postgres additionally lets you choose ON COMMIT PRESERVE ROWS (default, keeps data until the session ends) or ON COMMIT DROP (table vanishes at the end of the transaction that created it).

Worked Example — Breaking Up a Complex Report

Instead of one deeply nested query that's hard to read and optimize, a report can be broken into staged temp tables:

sql
CREATE TEMP TABLE step1_regional_totals AS
SELECT region, SUM(amount) AS total FROM sales GROUP BY region;

CREATE TEMP TABLE step2_ranked AS
SELECT region, total, RANK() OVER (ORDER BY total DESC) AS rnk
FROM step1_regional_totals;

SELECT * FROM step2_ranked WHERE rnk <= 3;

Each step is easy to inspect and debug independently — a real advantage over a single giant nested subquery, at the cost of extra I/O for the intermediate materializations (a trade-off WITH CTEs sometimes address instead, depending on the engine's optimizer).

Edge Cases and Pitfalls

• `tempdb` contention (SQL Server): heavy temp-table usage across many concurrent sessions can make tempdb itself a serious performance bottleneck — a well-known SQL Server operational concern.
• Not covered by regular backups: temporary tables are, by design, excluded from standard database backup/restore processes.
• Connection pooling surprises: an application using a pooled connection may create a temp table, then a subsequent "session" (actually a different pooled physical connection) can't see it, producing confusing "table not found" errors — a frequent bug in ORMs/pooled environments.
• Constraint limitations: some engines restrict or disallow foreign keys on temporary tables referencing permanent tables (and vice versa).
• Naming collisions across sessions are generally fine (isolated namespaces) but a ##global_temp table in SQL Server is genuinely shared and can collide if two sessions pick the same name.

Key Takeaways / Q&A

Q: What's the difference between SQL Server's #temp and ##temp tables? A: #temp is local — visible only to the session that created it. ##temp is global — visible to all sessions until every session referencing it disconnects.

Q: Are temporary tables backed up like normal tables? A: No — they are excluded from standard backup/restore processes since they're inherently transient.

Q: Why did my ORM throw "table not found" for a temp table I just created? A: Likely a connection-pooling issue — a different physical connection (which the pool treats as available) doesn't see a temp table created on another connection/session.