Temporary tables and table variables: persist results during a process

Some tasks need to separate a complex query into stages, reuse a set, or inspect it before modifying data. SQL Server offers temporary tables and table variables to retain results for a limited scope. Scope is the part of execution where a name exists and can be used.

Neither is automatically better. The choice depends on volume, later operations, indexes, and the execution plan. The optimizer is the SQL Server component that compares strategies, and the plan describes the strategy selected.

The idea

A local temporary table uses a name that begins with # and lives in tempdb, the system database SQL Server uses for temporary objects and work, for the corresponding session or scope.
A table variable is declared with @ and respects the scope of the batch, procedure or function where it was created.

Both store data in tempdb; The word variable does not mean that everything remains solely in memory.

Example: temporary table

A local temporary table is created with CREATE TABLE and a name beginning with #. Its definition can include types, nullability, constraints, and indexes.

CREATE TABLE #TablaTemporal
(
    Columna TipoDeDato
);

CREATE TABLE #PedidosRecientes
(
    IdPedido BIGINT NOT NULL PRIMARY KEY,
    IdCliente INT NOT NULL,
    FechaPedido DATETIME2(0) NOT NULL
);

INSERT INTO #PedidosRecientes
    (IdPedido, IdCliente, FechaPedido)
SELECT
    p.IdPedido,
    p.IdCliente,
    p.FechaPedido
FROM dbo.Pedido AS p
WHERE p.FechaPedido >= '2026-03-01';

SELECT
    c.Nombre,
    COUNT(*) AS CantidadPedidos
FROM #PedidosRecientes AS pr
INNER JOIN dbo.Cliente AS c
    ON c.IdCliente = pr.IdCliente
GROUP BY c.Nombre;

DROP TABLE #PedidosRecientes;

The table can be queried multiple times within its scope. We can also add indexes when subsequent operations justify it.

Example: table variable

A table variable is declared with DECLARE, a name beginning with @, and the word TABLE. Its structure is defined in the same declaration.

DECLARE @VariableTabla TABLE
(
    Columna TipoDeDato
);

DECLARE @ProductosSeleccionados TABLE
(
    IdProducto INT NOT NULL PRIMARY KEY,
    Precio DECIMAL(12, 2) NOT NULL
);

INSERT INTO @ProductosSeleccionados (IdProducto, Precio)
SELECT
    p.IdProducto,
    p.Precio
FROM dbo.Producto AS p
WHERE p.Activo = 1
  AND p.Precio >= 500;

SELECT
    ps.IdProducto,
    ps.Precio
FROM @ProductosSeleccionados AS ps;

The variable is convenient for small sets and limited use. Its definition is part of the declaration and does not need a DROP.

Practical comparison

Appearance	Temporary table	Table variable
Name	`#Resultado`	`@Resultado`
Typical range	Session or procedure	Batch, procedure or function
Statistics	Yes, depending on the object and use	More limited
Later indices	Allows you to create them	Mainly through the definition
Reuse in multiple instructions	Yes	Yes, within your reach

Recent SQL Server versions can improve table-variable estimates through deferred compilation: delaying plan creation until the inserted rows are known. Even so, we should not assume that they behave like a temporary table at every volume.

There is also SELECT INTO, which creates and fills a temporary table from a query result. It is concise, but an explicit definition lets us control types, nullability—whether a column accepts NULL—and constraints before inserting.

What should be checked

We look at the actual number of rows, reuse, post joins, and execution plan. For large sets or sets queried multiple times, a temporary table with appropriate indexes usually gives the optimizer more options.

We must also avoid creating temporary stages out of habit. Each write and read adds work to tempdb; A single clear query may be enough.