SQL - Temporary Tables | Application Architect

Key Insights

Temporary tables provide session-isolated storage for intermediate results, automatically cleaned up when the session ends, making them ideal for complex multi-step queries and ETL processes
Local temporary tables (#table) are session-specific while global temporary tables (##table) are visible across sessions but still automatically dropped, with performance characteristics similar to permanent tables
Table variables (@table) offer lighter-weight alternatives for small datasets but lack statistics and indexes, making temp tables the better choice for operations involving more than a few hundred rows

What Are Temporary Tables

Temporary tables are database objects that store intermediate result sets during query execution. Unlike permanent tables, they exist only for the duration of a session or transaction and are automatically dropped when no longer needed. They reside in tempdb and provide the same functionality as regular tables—indexes, constraints, statistics—without cluttering your production schema.

-- Creating a basic temporary table
CREATE TABLE #CustomerOrders (
    CustomerID INT,
    OrderCount INT,
    TotalAmount DECIMAL(10,2),
    LastOrderDate DATE
);

-- Populating it
INSERT INTO #CustomerOrders
SELECT 
    CustomerID,
    COUNT(*) AS OrderCount,
    SUM(TotalAmount) AS TotalAmount,
    MAX(OrderDate) AS LastOrderDate
FROM Orders
WHERE OrderDate >= DATEADD(YEAR, -1, GETDATE())
GROUP BY CustomerID;

-- Using it in subsequent queries
SELECT c.CustomerName, co.OrderCount, co.TotalAmount
FROM Customers c
INNER JOIN #CustomerOrders co ON c.CustomerID = co.CustomerID
WHERE co.OrderCount > 10;

Local vs Global Temporary Tables

SQL Server distinguishes between local (#) and global (##) temporary tables. Local temporary tables are visible only to the connection that created them. Global temporary tables are visible to all connections but are dropped when the last connection referencing them closes.

-- Local temporary table (session-specific)
CREATE TABLE #LocalTemp (
    ID INT,
    Value VARCHAR(50)
);

-- Global temporary table (visible to all sessions)
CREATE TABLE ##GlobalTemp (
    ID INT,
    Value VARCHAR(50)
);

-- Check temporary table existence
IF OBJECT_ID('tempdb..#LocalTemp') IS NOT NULL
    DROP TABLE #LocalTemp;

-- Global temp tables persist across sessions
-- Session 1:
CREATE TABLE ##SharedData (ProcessID INT, Status VARCHAR(20));
INSERT INTO ##SharedData VALUES (1, 'Processing');

-- Session 2 can access it:
SELECT * FROM ##SharedData;
UPDATE ##SharedData SET Status = 'Complete' WHERE ProcessID = 1;

Performance Optimization with Indexes

Temporary tables support indexes just like permanent tables. For large result sets, adding indexes significantly improves query performance.

-- Create temp table with primary key
CREATE TABLE #ProductSales (
    ProductID INT PRIMARY KEY,
    CategoryID INT,
    TotalSales DECIMAL(12,2),
    UnitsSold INT
);

-- Add non-clustered index
CREATE NONCLUSTERED INDEX IX_Category 
ON #ProductSales(CategoryID) 
INCLUDE (TotalSales);

-- Insert large dataset
INSERT INTO #ProductSales
SELECT 
    ProductID,
    CategoryID,
    SUM(SalesAmount) AS TotalSales,
    SUM(Quantity) AS UnitsSold
FROM Sales
WHERE SaleDate >= '2023-01-01'
GROUP BY ProductID, CategoryID;

-- Query benefits from indexes
SELECT CategoryID, SUM(TotalSales) AS CategoryTotal
FROM #ProductSales
WHERE CategoryID IN (1, 3, 5)
GROUP BY CategoryID;

Temporary Tables vs Table Variables

Table variables (@table) appear similar but have different use cases. Table variables lack statistics, making them suitable only for small datasets (typically under 100 rows). Temporary tables maintain statistics, enabling the query optimizer to generate better execution plans.

-- Table variable (no statistics)
DECLARE @SmallSet TABLE (
    ID INT,
    Name VARCHAR(50)
);

INSERT INTO @SmallSet VALUES (1, 'Item1'), (2, 'Item2');

-- Temporary table (with statistics)
CREATE TABLE #LargeSet (
    ID INT,
    Name VARCHAR(50),
    CategoryID INT,
    Amount DECIMAL(10,2)
);

INSERT INTO #LargeSet
SELECT ID, Name, CategoryID, Amount
FROM Products
WHERE IsActive = 1;

-- Update statistics explicitly if needed
UPDATE STATISTICS #LargeSet;

-- Table variables cannot be explicitly indexed (except inline)
DECLARE @OrderSummary TABLE (
    OrderID INT PRIMARY KEY,  -- Inline constraint allowed
    CustomerID INT,
    Total DECIMAL(10,2)
);

Complex ETL Scenarios

Temporary tables excel in multi-step ETL processes where you need to transform data through several stages before final insertion.

-- Stage 1: Extract raw data
CREATE TABLE #RawCustomerData (
    SourceID INT,
    FullName VARCHAR(100),
    Email VARCHAR(100),
    PhoneRaw VARCHAR(50),
    AddressLine VARCHAR(200)
);

INSERT INTO #RawCustomerData
SELECT SourceID, FullName, Email, PhoneRaw, AddressLine
FROM ExternalDataSource
WHERE ImportDate = CAST(GETDATE() AS DATE);

-- Stage 2: Clean and normalize
CREATE TABLE #CleanedData (
    SourceID INT,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Email VARCHAR(100),
    Phone VARCHAR(20),
    City VARCHAR(50),
    State VARCHAR(2),
    ZipCode VARCHAR(10)
);

INSERT INTO #CleanedData
SELECT 
    SourceID,
    LTRIM(RTRIM(SUBSTRING(FullName, 1, CHARINDEX(' ', FullName)))) AS FirstName,
    LTRIM(RTRIM(SUBSTRING(FullName, CHARINDEX(' ', FullName) + 1, 100))) AS LastName,
    LOWER(Email) AS Email,
    dbo.FormatPhone(PhoneRaw) AS Phone,
    dbo.ParseCity(AddressLine) AS City,
    dbo.ParseState(AddressLine) AS State,
    dbo.ParseZip(AddressLine) AS ZipCode
FROM #RawCustomerData;

-- Stage 3: Identify duplicates
CREATE TABLE #Duplicates (
    SourceID INT,
    Email VARCHAR(100),
    DuplicateCount INT
);

INSERT INTO #Duplicates
SELECT MIN(SourceID), Email, COUNT(*)
FROM #CleanedData
GROUP BY Email
HAVING COUNT(*) > 1;

-- Stage 4: Final insert with deduplication
INSERT INTO Customers (FirstName, LastName, Email, Phone, City, State, ZipCode)
SELECT DISTINCT c.FirstName, c.LastName, c.Email, c.Phone, c.City, c.State, c.ZipCode
FROM #CleanedData c
LEFT JOIN #Duplicates d ON c.Email = d.Email
WHERE d.Email IS NULL OR c.SourceID = d.SourceID;

Recursive Operations and Hierarchies

Temporary tables work well with recursive CTEs when you need to materialize hierarchical data for multiple queries.

-- Build employee hierarchy
CREATE TABLE #EmployeeHierarchy (
    EmployeeID INT,
    ManagerID INT,
    EmployeeName VARCHAR(100),
    Level INT,
    HierarchyPath VARCHAR(500)
);

WITH OrgChart AS (
    -- Anchor: Top level employees
    SELECT 
        EmployeeID,
        ManagerID,
        EmployeeName,
        0 AS Level,
        CAST(EmployeeName AS VARCHAR(500)) AS HierarchyPath
    FROM Employees
    WHERE ManagerID IS NULL
    
    UNION ALL
    
    -- Recursive: All subordinates
    SELECT 
        e.EmployeeID,
        e.ManagerID,
        e.EmployeeName,
        oc.Level + 1,
        CAST(oc.HierarchyPath + ' > ' + e.EmployeeName AS VARCHAR(500))
    FROM Employees e
    INNER JOIN OrgChart oc ON e.ManagerID = oc.EmployeeID
)
INSERT INTO #EmployeeHierarchy
SELECT * FROM OrgChart;

-- Now query the hierarchy multiple times efficiently
SELECT * FROM #EmployeeHierarchy WHERE Level = 2;
SELECT ManagerID, COUNT(*) FROM #EmployeeHierarchy GROUP BY ManagerID;

Transaction Scope and Cleanup

Temporary tables respect transaction boundaries. If a transaction rolls back, any data inserted into the temp table during that transaction is also rolled back, but the table structure remains.

CREATE TABLE #TransactionTest (
    ID INT,
    Value VARCHAR(50)
);

BEGIN TRANSACTION;
    INSERT INTO #TransactionTest VALUES (1, 'First');
    INSERT INTO #TransactionTest VALUES (2, 'Second');
ROLLBACK TRANSACTION;

-- Table exists but is empty
SELECT * FROM #TransactionTest;  -- Returns 0 rows

-- Explicit cleanup (usually unnecessary)
IF OBJECT_ID('tempdb..#TransactionTest') IS NOT NULL
    DROP TABLE #TransactionTest;

-- Temp tables auto-drop at session end
-- But explicit drops free tempdb space immediately
DROP TABLE #EmployeeHierarchy;
DROP TABLE #CleanedData;
DROP TABLE #RawCustomerData;

Best Practices

Use temporary tables when working with intermediate results exceeding a few hundred rows, especially when you’ll query those results multiple times. Add appropriate indexes based on your query patterns. For simple, single-use operations with small datasets, table variables or CTEs may be more appropriate.

Monitor tempdb usage in production environments, as heavy temporary table usage can create contention. Consider partitioning strategies for very large temporary datasets, and always explicitly drop temporary tables in long-running procedures to free resources immediately rather than waiting for session cleanup.