Local Temp Tables in SQL Server (#temp): Scope, Statistics, and Real Use Cases

Fundamentals Chapter 5 gave you a first look at local temp tables. This chapter goes further: the full scope rules, why they get real optimizer statistics (unlike table variables, next lesson), and where they genuinely earn their place over a CTE or subquery.

Using One

CREATE TABLE #DriverTotals (
    driver_name NVARCHAR(100),
    total_fare  DECIMAL(10,2),
    trip_count  INT
);

INSERT INTO #DriverTotals
SELECT driver_name, SUM(fare_usd), COUNT(*)
FROM dbo.TripAdvanced
GROUP BY driver_name;

-- Can be indexed, just like a real table
CREATE CLUSTERED INDEX IX_Temp_DriverName ON #DriverTotals (driver_name);

SELECT * FROM #DriverTotals WHERE total_fare > 30;

DROP TABLE #DriverTotals;

Real Statistics, Real Indexes

This is what makes local temp tables genuinely useful for staging large intermediate results in a complex report query — the optimizer isn’t flying blind the way it is with a table variable (next lesson makes this contrast concrete). SQL Server automatically creates and updates statistics on a #temp table’s data, exactly as it would for a permanent table, which means row-count estimates for anything querying it afterward are generally accurate.

The Scope Rule, Precisely

A local temp table created inside a stored procedure is visible to that procedure and anything it calls (nested procedures can see and use a caller’s #temp table — a genuinely useful pattern for passing staged data down a call chain), but disappears when the outermost creating scope ends. If two different sessions both create #DriverTotals, SQL Server silently gives them separate, isolated copies internally (renamed behind the scenes with a unique suffix) — they never collide, and neither session can see the other’s version.

CREATE PROCEDURE dbo.OuterProc AS
BEGIN
    CREATE TABLE #Shared (id INT);
    INSERT INTO #Shared VALUES (1);
    EXEC dbo.InnerProc; -- InnerProc can see and use #Shared
END;
GO
CREATE PROCEDURE dbo.InnerProc AS
BEGIN
    SELECT * FROM #Shared; -- works: nested procs inherit the caller's temp tables
END;

When a Temp Table Beats a CTE

A CTE (covered in full in Chapter 6) is often the more elegant choice for a single query — but it’s re-evaluated wherever it’s referenced within that query, and doesn’t persist statistics or an index of its own. A local temp table earns its place specifically when: the same intermediate result is queried multiple times across several statements, the intermediate result is large enough that having a real index on it matters, or you need to break a genuinely complex multi-stage transformation into readable, individually-testable steps within a longer script or procedure.