Local vs global temporary tables and table variables

Overview

Temporary tables and table variables are SQL Server tools for storing intermediate rows while a query, stored procedure, batch, or workflow is running. They are commonly used for staging data, breaking complex queries into readable steps, capturing output rows, preparing reporting results, and simplifying procedural database logic.

Local temporary tables, global temporary tables, and table variables all look table-like, but they differ in scope, lifetime, optimizer behavior, indexing options, transaction behavior, and visibility across sessions or dynamic SQL. Those differences can decide whether a query is simple and fast or unexpectedly slow and difficult to debug.

This topic matters in real applications because SQL code often needs a temporary working set before inserting final results, validating imports, calculating totals, or joining against a filtered subset of data. Choosing the wrong temporary structure can create contention, wrong assumptions about visibility, poor execution plans, or hard-to-reproduce production behavior.

For interviews, strong candidates can explain the difference between #temp tables, ##global temp tables, and @table variables; choose the right option for small versus large row sets; and describe how scope, statistics, indexing, recompilation, and transactions affect performance and correctness.

Core Concepts

Temporary Data Structures In SQL Server

SQL Server supports several ways to hold temporary relational data:

• Local temporary tables, such as #OrderIds
• Global temporary tables, such as ##ImportProgress
• Table variables, such as @OrderIds
• Common table expressions and derived tables for single-statement logic
• Permanent staging tables for durable, shared, or audited workflows

The first interview skill is knowing that these tools are not interchangeable. They solve overlapping problems, but each has a different scope and cost model.

Example use cases:

• Store a filtered list of customer IDs before applying multiple joins.
• Stage rows imported from a file before validating and merging them.
• Capture rows affected by an UPDATE using the OUTPUT clause.
• Split a large stored procedure into readable phases.
• Share a short-lived result with another session or diagnostic process.

Local Temporary Tables

A local temporary table is created with a single number sign prefix, such as #RecentOrders. It is visible only to the session that creates it, plus nested stored procedures executed by that session.

Example:

CREATE TABLE #RecentOrders
(
    OrderId BIGINT NOT NULL PRIMARY KEY,
    CustomerId BIGINT NOT NULL,
    OrderDate DATETIME2 NOT NULL
);

INSERT INTO #RecentOrders (OrderId, CustomerId, OrderDate)
SELECT OrderId, CustomerId, OrderDate
FROM dbo.Orders
WHERE OrderDate >= DATEADD(day, -30, SYSUTCDATETIME());

SELECT c.CustomerName, COUNT(*) AS RecentOrderCount
FROM #RecentOrders AS ro
JOIN dbo.Customers AS c
    ON c.CustomerId = ro.CustomerId
GROUP BY c.CustomerName;

Local temp tables are useful when intermediate data is reused more than once, row counts may be meaningful, or indexes and statistics are important to query performance.

Common characteristics:

• Created in tempdb.
• Scoped to the current session.
• Automatically dropped when the session ends.
• Can be explicitly dropped with DROP TABLE #RecentOrders.
• Can have indexes, constraints, and statistics.
• Can be referenced by nested stored procedures called by the session.
• Can often produce better execution plans than table variables for larger or repeatedly joined data sets.

Global Temporary Tables

A global temporary table is created with two number sign prefixes, such as ##ImportProgress. It is visible to all sessions while it exists.

Example:

CREATE TABLE ##ImportProgress
(
    ImportId UNIQUEIDENTIFIER NOT NULL,
    StepName NVARCHAR(100) NOT NULL,
    RowsProcessed INT NOT NULL,
    UpdatedAt DATETIME2 NOT NULL
);

INSERT INTO ##ImportProgress (ImportId, StepName, RowsProcessed, UpdatedAt)
VALUES (NEWID(), N'Validate rows', 1200, SYSUTCDATETIME());

Global temp tables are less common in application code because shared temporary state introduces naming collisions, lifecycle surprises, and concurrency risks. They can be useful for administrative scripts, diagnostics, cross-session troubleshooting, or controlled workflows where multiple sessions must see the same temporary data.

Common characteristics:

• Created in tempdb.
• Visible to all sessions.
• Dropped when the creating session ends and the last active statement referencing it finishes.
• Can have indexes and constraints.
• Need careful naming to avoid collisions.
• Usually should not be used as a general application coordination mechanism.

Table Variables

A table variable is declared using the table data type. It behaves like a local variable whose value is a set of rows.

Example:

DECLARE @ChangedOrders TABLE
(
    OrderId BIGINT NOT NULL PRIMARY KEY,
    OldStatus NVARCHAR(30) NOT NULL,
    NewStatus NVARCHAR(30) NOT NULL
);

UPDATE dbo.Orders
SET Status = N'Archived'
OUTPUT inserted.OrderId, deleted.Status, inserted.Status
INTO @ChangedOrders
WHERE Status = N'Completed'
  AND CompletedAt < DATEADD(year, -2, SYSUTCDATETIME());

SELECT OrderId, OldStatus, NewStatus
FROM @ChangedOrders;

Table variables are often a good fit for small row sets, simple logic, and cases where variable-like scope is useful. They are also common for capturing OUTPUT rows from DML statements.

Common characteristics:

• Declared with DECLARE @Name TABLE (...).
• Scoped to the batch, stored procedure, or function where they are declared.
• Automatically cleaned up when that scope ends.
• Can be used in functions.
• Can define primary key, unique, nullability, check, and default constraints in the declaration.
• Cannot be altered after declaration.
• Cannot be the target of SELECT ... INTO.
• Cannot be referenced by dynamic SQL outside the scope where the table variable is declared.

Scope And Lifetime

Scope is one of the most important differences.

Local temp table scope:

CREATE TABLE #Ids (Id INT NOT NULL PRIMARY KEY);

EXEC dbo.ProcessIds; -- A nested procedure can reference #Ids if it knows the name.

The #Ids table exists for the creating session. Nested procedures can access it, but other sessions cannot.

Table variable scope:

DECLARE @Ids TABLE (Id INT NOT NULL PRIMARY KEY);

EXEC sys.sp_executesql N'SELECT COUNT(*) FROM @Ids';

The dynamic SQL example fails because the table variable is not in scope inside the separate dynamic SQL batch.

Global temp table scope:

CREATE TABLE ##SharedIds (Id INT NOT NULL PRIMARY KEY);

Other sessions can reference ##SharedIds while it exists. That visibility is powerful, but it also means the name is shared server-wide within tempdb.

Optimizer Behavior, Statistics, And Cardinality

Optimizer behavior is a major interview topic.

Local temp tables can have statistics, and SQL Server can use those statistics to estimate row counts. This matters when the temp table is joined to other tables or used multiple times.

Table variables historically had weaker cardinality information. SQL Server documentation warns that table variables do not have distribution statistics, which can lead the optimizer to choose a plan based on poor row-count assumptions. Newer compatibility levels include deferred compilation improvements, but table variables still have restrictions compared with temp tables.

Practical implication:

DECLARE @Ids TABLE (CustomerId BIGINT NOT NULL PRIMARY KEY);

INSERT INTO @Ids (CustomerId)
SELECT CustomerId
FROM dbo.Customers
WHERE IsActive = 1;

SELECT o.OrderId, o.Total
FROM dbo.Orders AS o
JOIN @Ids AS i
    ON i.CustomerId = o.CustomerId;

If @Ids has only 10 rows, this may be fine. If it has 500,000 rows, the optimizer may not choose the best plan. A local temp table with indexes and statistics may be safer:

CREATE TABLE #Ids
(
    CustomerId BIGINT NOT NULL PRIMARY KEY
);

INSERT INTO #Ids (CustomerId)
SELECT CustomerId
FROM dbo.Customers
WHERE IsActive = 1;

SELECT o.OrderId, o.Total
FROM dbo.Orders AS o
JOIN #Ids AS i
    ON i.CustomerId = o.CustomerId;

Indexing And Constraints

Local and global temp tables can be indexed like regular tables.

Example:

CREATE TABLE #OrderWork
(
    OrderId BIGINT NOT NULL PRIMARY KEY,
    CustomerId BIGINT NOT NULL,
    Status NVARCHAR(30) NOT NULL,
    CreatedAt DATETIME2 NOT NULL
);

CREATE INDEX IX_OrderWork_CustomerId
ON #OrderWork (CustomerId);

Table variables can declare some constraints and inline indexes as part of the declaration, but they do not support the same full post-creation flexibility.

Example:

DECLARE @OrderWork TABLE
(
    OrderId BIGINT NOT NULL PRIMARY KEY,
    CustomerId BIGINT NOT NULL,
    Status NVARCHAR(30) NOT NULL,
    CreatedAt DATETIME2 NOT NULL,
    INDEX IX_OrderWork_CustomerId (CustomerId)
);

Interview rule of thumb: if you need several indexes, meaningful statistics, or repeated joins against many rows, start by considering a temp table.

Transactions And Rollback Behavior

Temporary tables participate more like regular tables inside transactions.

Example:

CREATE TABLE #AuditWork
(
    Id INT NOT NULL PRIMARY KEY
);

BEGIN TRANSACTION;

INSERT INTO #AuditWork (Id)
VALUES (1);

ROLLBACK TRANSACTION;

SELECT *
FROM #AuditWork;

The inserted row is rolled back.

Table variables have more limited transaction behavior. SQL Server documentation describes table variables as requiring fewer locking and logging resources, and transaction rollbacks do not affect them in the same way as regular or temporary table data.

Example:

DECLARE @AuditWork TABLE
(
    Id INT NOT NULL PRIMARY KEY
);

BEGIN TRANSACTION;

INSERT INTO @AuditWork (Id)
VALUES (1);

ROLLBACK TRANSACTION;

SELECT *
FROM @AuditWork;

In SQL Server, the table variable itself remains in scope, and rollback behavior can surprise developers who expect it to behave exactly like a temp table. For workflows where rollback semantics of staged rows matter, use a temp table and test the behavior explicitly.

Dynamic SQL Visibility

Dynamic SQL often exposes scope differences.

A local temp table created before dynamic SQL can be visible inside that dynamic SQL:

CREATE TABLE #Ids (Id INT NOT NULL PRIMARY KEY);
INSERT INTO #Ids (Id) VALUES (1), (2), (3);

EXEC sys.sp_executesql N'
    SELECT COUNT(*) AS IdCount
    FROM #Ids;
';

A table variable declared outside the dynamic SQL cannot be referenced inside that dynamic SQL batch:

DECLARE @Ids TABLE (Id INT NOT NULL PRIMARY KEY);
INSERT INTO @Ids (Id) VALUES (1), (2), (3);

EXEC sys.sp_executesql N'
    SELECT COUNT(*) AS IdCount
    FROM @Ids;
';

This fails because @Ids is outside the dynamic SQL scope. If dynamic SQL must work with table-shaped data, common choices include temp tables, table-valued parameters, JSON input, or creating the table variable inside the dynamic SQL batch.

Choosing Between The Three

Use a local temp table when:

• The row count may be large.
• The intermediate data is joined multiple times.
• You need indexes, statistics, or better optimizer estimates.
• You need dynamic SQL or nested procedures to access the data.
• Transaction rollback behavior matters.

Use a table variable when:

• The row count is small.
• The logic is simple.
• You want variable-like scope.
• You are capturing DML OUTPUT.
• You are writing a function where temp tables are not allowed.

Use a global temp table when:

• Multiple sessions genuinely need to access the same temporary data.
• You are writing a controlled administrative script.
• You are doing diagnostics or troubleshooting.
• You can safely manage naming, concurrency, and cleanup.

Avoid global temp tables for normal web application request processing. They create shared state, and shared state is usually a liability in concurrent systems.

Temporary Tables Vs CTEs

A common interview comparison is temp tables versus CTEs.

A CTE is usually scoped to a single statement:

WITH RecentOrders AS
(
    SELECT OrderId, CustomerId, OrderDate
    FROM dbo.Orders
    WHERE OrderDate >= DATEADD(day, -30, SYSUTCDATETIME())
)
SELECT CustomerId, COUNT(*) AS OrderCount
FROM RecentOrders
GROUP BY CustomerId;

This is clean when the intermediate result is used once. A temp table is often better when the result is expensive to produce, reused across multiple statements, or needs indexing.

Common Mistakes

Common mistakes include:

• Using a table variable for thousands or millions of rows and then blaming the join.
• Assuming table variables always live only in memory.
• Expecting a table variable to be visible inside dynamic SQL.
• Using a global temp table for normal application request state.
• Forgetting to add indexes to temp tables that are joined repeatedly.
• Assuming #Temp names are unique without considering nested procedure behavior.
• Not testing with realistic row counts.
• Leaving temp table creation and query logic in one huge procedure that is difficult to reason about.

Best Practices

Best practices:

• Prefer the simplest structure that preserves correctness and performance.
• Use local temp tables for medium-to-large staged data sets.
• Use table variables for small, simple, scoped row sets.
• Add indexes to temp tables based on real query predicates and join columns.
• Avoid global temp tables unless cross-session visibility is explicitly required.
• Explicitly drop temp tables when it improves readability or reduces long-running resource usage.
• Benchmark with realistic row counts, not toy examples.
• Check actual execution plans when table variables participate in joins.
• Consider table-valued parameters when passing row sets from application code into stored procedures.