SQL Server Temp Tables: A First Look Before You Need the Full Picture

Sometimes a problem is genuinely easier to solve in two steps than one giant nested query. Temp tables are SQL Server’s answer to “I need somewhere to put an intermediate result while I keep working.”

A Local Temp Table in Action

CREATE TABLE #HighValueTrips (
    trip_id INT,
    fare_usd DECIMAL(8,2)
);

INSERT INTO #HighValueTrips
SELECT trip_id, fare_usd FROM dbo.Trip WHERE fare_usd > 20;

SELECT * FROM #HighValueTrips;

DROP TABLE #HighValueTrips;

Notice this is genuinely a real table — it has its own CREATE TABLE, accepts INSERT, and can be queried, filtered, and even joined to other tables exactly like a permanent one, for as long as your session lasts.

What Makes It “Temporary”

A local temp table (prefixed with #) physically lives in the special system database tempdb, not your regular database — but is visible only to the session that created it, and is automatically cleaned up when that session ends, or you can drop it explicitly as shown above. Two sessions can both create a table named #Scratch at the same time without any conflict; SQL Server keeps them completely separate internally.

A Realistic Two-Step Use Case

-- Step 1: capture an expensive-to-compute intermediate result once
SELECT driver_id, COUNT(*) AS trip_count, SUM(fare_usd) AS total_earned
INTO #DriverSummary
FROM dbo.Trip
GROUP BY driver_id;

-- Step 2: reuse it multiple times without recomputing the aggregation
SELECT * FROM #DriverSummary WHERE trip_count > 5;
SELECT d.full_name, s.total_earned
FROM dbo.Driver d JOIN #DriverSummary s ON d.driver_id = s.driver_id
ORDER BY s.total_earned DESC;

SELECT ... INTO creates the temp table and populates it in one statement, inferring column types automatically — a common shortcut once you’re comfortable with the explicit CREATE TABLE form shown earlier.

Why Not Just Use a Bigger Subquery?

You often could. The tradeoff: a temp table computes its result once and lets you reuse and re-query it as many times as needed; a subquery or CTE re-runs its logic each time it’s referenced (with some caveats the advanced course covers). For a genuinely expensive intermediate calculation you need to reuse several times in a longer script, a temp table can be both clearer to read and faster to run.

Just the Beginning

This is a preview — the full comparison of local temp tables, table variables, and global temp tables (prefixed ##, visible across all sessions), including exactly when each one is the right tool for a given job and their real performance differences, is a dedicated chapter (Chapter 3) in SQL Server for Developers & DBAs. For now, know that temp tables exist and behave like session-scoped scratch space you can CREATE, INSERT into, query, and DROP just like any other table.