Temp Table vs Table Variable vs CTE in SQL Server

Every T-SQL developer hits this fork in the road eventually: you need to stash some intermediate result set inside a stored procedure, and you have three built-in options. You can create a #temp table, declare a @table variable, or wrap the logic in a common table expression (CTE — a named, temporary result set defined with a WITH clause that exists only for the duration of the query that references it). Most tutorials give you a syntax rundown and a vague "it depends" and call it a day.

That's not enough to make good decisions in a production stored procedure that runs thousands of times a day. The real answer to the temp table vs table variable vs CTE SQL Server question depends on row counts, whether the optimizer can generate accurate statistics, how the object interacts with the query plan cache, and what happens when a transaction rolls back. This article walks through each of those angles with concrete examples so you can pick correctly instead of guessing — using the query optimizer behavior current in SQL Server 2022, SQL Server 2025, Azure SQL Database, and Azure SQL Managed Instance, which all share the same engine for these three constructs.

Overview: What Each Option Actually Is

A temp table (#TempTable for session-scoped, ##TempTable for global) is a real table created in tempdb, the system database SQL Server uses for temporary storage. It behaves like any other table — it supports indexes, constraints, primary keys, and statistics (metadata about data distribution that the query optimizer uses to estimate row counts). It's visible to the current session and any nested procedures called from it. When created inside a stored procedure, it is automatically dropped when that procedure finishes executing, whereas a temp table created in an ad-hoc batch persists until the session ends or it is explicitly dropped.

A table variable (@TableVariable) is also backed by tempdb under the hood, despite the common misconception that it lives entirely in memory. It's declared like a variable, scoped to the batch, function, or stored procedure in which it's defined, and it goes out of scope automatically — no explicit DROP needed.

On any current platform — SQL Server 2022, SQL Server 2025, Azure SQL Database, or Azure SQL Managed Instance — a table variable's row estimate is better than the flat guess it used to be. All of these support table variable deferred compilation (available since SQL Server 2019 under database compatibility level 150, and unchanged under SQL Server 2022's default level of 160 and SQL Server 2025's default level of 170), which defers compiling the statement that references the table variable until it first actually runs, so the optimizer can see the real row count instead of guessing. That's a cardinality fix, not a statistics fix — table variables still don't get column-level distribution statistics (histograms) the way temp tables do, which I'll get into below.

It also creates a plan-caching problem a lot like parameter sniffing, even though a table variable isn't a parameter: the plan is compiled and cached based on whatever row count the table variable happened to have on that first execution, and later executions reuse that same plan even if the row count is wildly different. A stored procedure that first runs with 5 rows in the table variable and later runs with 100,000 will keep reusing the 5-row plan until something forces a recompile.

If you're troubleshooting an older instance: table variables originally had no statistics at all and always assumed exactly 1 row. SQL Server 2014's 100-row default estimate is a common point of confusion here — it applied only to multi-statement table-valued functions, not to plain table variables.

Regardless of version, one practical mitigation is worth knowing: adding OPTION (RECOMPILE) to the statement that queries the table variable forces SQL Server to compile that statement using the actual row count at execution time instead of reusing a cached estimate. It doesn't give the optimizer a histogram, but it does eliminate the stale-plan-reuse problem described above — at the cost of paying a compilation cost on every execution, which matters for statements that run very frequently.

A CTE is not a physical storage object at all. It's a named subquery — syntactic sugar that lets you reference a result set by name within a single statement. It has no persistent storage, no indexes, and no statistics of its own. Unlike some other database engines, SQL Server does not materialize a non-recursive CTE to cache and reuse its result across multiple references — the optimizer inlines the CTE definition into the surrounding query, so the underlying logic is evaluated again every time the CTE is referenced.

The choice matters most in stored procedures that process meaningful row counts, run repeatedly, or need to survive across multiple statements or transaction boundaries — which rules out CTEs immediately for a lot of real-world scenarios, since a CTE can't be referenced outside the single statement it's attached to.

Key Differences

Here's a direct comparison of the three across the dimensions that actually affect production behavior.

Dimension	Temp Table (#temp)	Table Variable (@table)	CTE
Storage location	tempdb, physical	tempdb, physical	None — inline or materialized as part of the parent query
Scope	Session (visible to nested procs)	Batch/procedure only	Single statement only
Statistics	Full column and index statistics, auto-updated	No distribution statistics (histograms), ever. On SQL Server 2022, SQL Server 2025, Azure SQL Database, and Azure SQL Managed Instance, deferred compilation gives the optimizer the table variable's actual cardinality instead of a fixed guess	None — relies entirely on the base tables it references
Indexes	Explicit indexes, including nonclustered	Only via inline PRIMARY KEY / UNIQUE constraints at declaration	Not applicable
Transaction/rollback behavior	Participates in transactions; rolled back with ROLLBACK	Does not participate in transactions; changes survive ROLLBACK	Not applicable — no state to roll back
Recompilation	Can trigger statement-level recompiles when row counts cross specific thresholds	Does not trigger recompiles based on data volume changes	Recompiled with the parent query each execution
Recursive queries	Not supported directly	Not supported directly	Supported (WITH cte AS (... UNION ALL ...))
Reusability across batches	Yes, within the session	No, scoped to a single batch/procedure	No, single use

The single most misunderstood row in that table is "Statistics." A lot of older blog posts state flatly that table variables never get statistics and always assume one row. That's outdated advice for the platforms most teams are running today — deferred compilation, covered above, means the optimizer usually isn't flying blind anymore. Don't swing too far the other way, though: it's still a cardinality estimate rather than a real statistics object, there's no column-level histogram behind it, and — as noted above — that estimate can go stale the moment row counts shift between executions. Table variables are meaningfully better than they used to be. They're still not equivalent to temp tables for estimation purposes.

Performance: Where Cardinality Estimation Actually Bites You

The performance conversation almost always comes down to cardinality estimation — the optimizer's guess at how many rows a given step in the query plan will produce. Get that guess wrong and everything downstream, from join strategy to memory grant size, can be wrong too.

Temp tables carry real statistics. When you insert 500,000 rows into a #temp table and then join it to another table, SQL Server can (and in most configurations, will) auto-create statistics on the columns used in the join or WHERE clause. That gives the optimizer a genuinely informed row estimate, which usually leads it to pick the right join type — a hash join for large sets, a nested loop for small ones.

-- Temp table: statistics get created, optimizer sees real row counts
CREATE TABLE #OrderStaging
(
    OrderId     INT PRIMARY KEY,
    CustomerId  INT NOT NULL,
    OrderTotal  DECIMAL(12,2) NOT NULL
);

INSERT INTO #OrderStaging (OrderId, CustomerId, OrderTotal)
SELECT o.OrderId, o.CustomerId, o.OrderTotal
FROM dbo.Orders AS o
WHERE o.OrderDate >= DATEADD(MONTH, -3, SYSUTCDATETIME());

CREATE NONCLUSTERED INDEX IX_OrderStaging_CustomerId
    ON #OrderStaging (CustomerId);

SELECT c.CustomerName, SUM(os.OrderTotal) AS TotalSpent
FROM #OrderStaging AS os
JOIN dbo.Customers AS c ON c.CustomerId = os.CustomerId
GROUP BY c.CustomerName;

If you check the actual execution plan for that join, the estimated row count next to the #OrderStaging scan should track reasonably close to the actual row count, because the optimizer has statistics to draw from. That's the whole advantage in one sentence: better estimates lead to better plan choices.

Now the same logic with a table variable:

-- Table variable: historically no statistics, weaker row estimates
DECLARE @OrderStaging TABLE
(
    OrderId     INT PRIMARY KEY,
    CustomerId  INT NOT NULL,
    OrderTotal  DECIMAL(12,2) NOT NULL
);

INSERT INTO @OrderStaging (OrderId, CustomerId, OrderTotal)
SELECT o.OrderId, o.CustomerId, o.OrderTotal
FROM dbo.Orders AS o
WHERE o.OrderDate >= DATEADD(MONTH, -3, SYSUTCDATETIME());

SELECT c.CustomerName, SUM(os.OrderTotal) AS TotalSpent
FROM @OrderStaging AS os
JOIN dbo.Customers AS c ON c.CustomerId = os.CustomerId
GROUP BY c.CustomerName;

Structurally this looks nearly identical, but the plan the optimizer builds can differ meaningfully once the row count climbs. In practice, teams often see table variables handle small sets fine — a few hundred rows or fewer — and then start causing bad plan choices (usually nested loop joins where a hash join would be far cheaper) once row counts move into the tens of thousands.

There's no single universal row-count cutoff that applies to every schema and workload, so treat any specific number you read, including the ones in this article, as a starting point for your own testing with SET STATISTICS IO, TIME ON and the actual execution plan, not a hard rule.

CTEs don't have their own statistics because they aren't physical objects — the optimizer just inlines the CTE definition into the surrounding query and estimates based on the underlying base tables. That means a CTE's "performance" is really just the performance of the query it expands into.

If you reference the same CTE twice in one statement, SQL Server does not cache and reuse the result — it expands the CTE definition and evaluates the underlying logic separately for each reference, which can be a real cost trap for anything nontrivial inside the CTE body.

-- Recursive CTE example: employee hierarchy traversal
WITH EmployeeHierarchy AS
(
    SELECT EmployeeId, ManagerId, EmployeeName, 0 AS HierarchyLevel
    FROM dbo.Employees
    WHERE ManagerId IS NULL

    UNION ALL

    SELECT e.EmployeeId, e.ManagerId, e.EmployeeName, eh.HierarchyLevel + 1
    FROM dbo.Employees AS e
    JOIN EmployeeHierarchy AS eh ON e.ManagerId = eh.EmployeeId
)
SELECT EmployeeId, EmployeeName, HierarchyLevel
FROM EmployeeHierarchy
OPTION (MAXRECURSION 100);

This is the one job a temp table or table variable genuinely can't do cleanly: recursive traversal of hierarchical data. The MAXRECURSION hint caps the recursion depth (default is 100; you can set it to any value from 1 to 32,767, or use 0 to remove the cap entirely, which is risky on bad data) and protects you from an infinite loop if the hierarchy has a cycle.

One nuance worth flagging if you're reading an actual execution plan: a recursive CTE's plan typically shows Index Spool or Lazy Spool operators, each carrying a WITH STACK property — that's how SQL Server implements the recursive iteration itself, not a sign that the optimizer is caching and reusing results the way a temp table's statistics would let it. A non-recursive CTE referenced multiple times still gets evaluated separately for each reference, as noted above; a spool showing up in a recursive CTE's plan isn't evidence that CTEs are generally materialized and cached.

On locking and tempdb contention: under heavy concurrent load, both temp tables and table variables write to tempdb, and both can contribute to contention on tempdb's system pages, particularly on instances where tempdb isn't configured with multiple data files. This is an instance-level tuning concern more than a construct-level one.

If you're seeing PAGELATCH waits tied to tempdb page allocation on SQL Server 2019 or later, running on-premises or on an Azure VM, it's worth enabling Memory-Optimized Metadata TempDB at the server level (ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;, which requires a SQL Server service restart to take effect), which moves temp table and table variable system metadata off disk pages and into memory-optimized structures.

Azure SQL Database sidesteps this specific contention pattern for standalone single databases, since each one gets its own isolated tempdb instead of sharing one across many databases. Databases placed in an elastic pool are the exception: every database in the pool shares one tempdb, though temporary objects created by one pooled database still aren't visible to the others in the pool. Azure SQL Managed Instance also shares a single tempdb across the whole instance.

One correction worth being explicit about: Memory-Optimized Metadata TempDB itself — the specific fix described above — currently isn't available on either Azure SQL Database or Azure SQL Managed Instance, according to Microsoft's own documentation. Treat it as an on-premises or Azure VM lever only, and re-check Microsoft's current documentation before assuming otherwise, since Azure service capabilities do change over time.

Developer Experience: Syntax, Tooling, and Transactional Behavior

Syntactically, temp tables and table variables both feel like ordinary CREATE TABLE / DECLARE plus standard DML. The friction shows up in edge cases. Table variables don't support ALTER TABLE after declaration, can't have nonclustered indexes added after creation (only inline at declaration time, and only in reasonably recent SQL Server versions), and don't support TRUNCATE TABLE. They also don't support SET IDENTITY_INSERT or DBCC CHECKIDENT — if a table variable's structure includes an IDENTITY column, there's no way to insert explicit values into it or reseed the counter, which is a common source of friction in ETL staging scripts that need that kind of control.

Temp tables support nearly everything a permanent table does, including adding indexes after the fact, running sp_rename, or checking existence with OBJECT_ID('tempdb..#TempTable') before creating it.

Transaction behavior is where a lot of developers get burned — usually in the opposite direction from what they expect. Table variables do not participate in explicit user transactions. If you modify a table variable inside a transaction and then hit a ROLLBACK, the changes to the table variable survive it. Temp tables behave the way you'd normally expect a table to behave: they fully participate in transactions, and a ROLLBACK undoes their modifications along with everything else in the transaction.

This difference is exactly why table variables show up so often in error-logging patterns — a stored procedure can catch a failure, roll back the real data changes, and still have a table variable full of diagnostic detail to inspect or persist afterward.

BEGIN TRY
    BEGIN TRANSACTION;

    DECLARE @ErrorLog TABLE (ErrorMessage NVARCHAR(4000), LoggedAt DATETIME2);

    -- This insert survives the rollback below, because table variables don't participate in transactions
    INSERT INTO @ErrorLog (ErrorMessage, LoggedAt)
    VALUES ('Starting risky operation', SYSUTCDATETIME());

    UPDATE dbo.Accounts SET Balance = Balance - 1000000 WHERE AccountId = 42;

    -- Simulate a failure
    THROW 51000, 'Simulated failure for demonstration', 1;

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;

    -- @ErrorLog's insert survived the rollback, so we can still inspect it here
    SELECT ErrorMessage, LoggedAt, ERROR_MESSAGE() AS CapturedError
    FROM @ErrorLog;
END CATCH;

That transaction independence is a real, documented feature of table variables, not folklore — but keep it scoped to what it actually gives you. A table variable's scope still ends when the batch or procedure ends, so if you need the log entries to persist after the session is gone (say, for a permanent audit table other people query later), you still need a real table.

Reserve the table-variable pattern for capturing diagnostic detail you want to inspect or act on within the same procedure that just rolled back, not as a substitute for permanent logging.

CTEs have the smallest footprint of the three when it comes to tooling friction: no cleanup, no explicit scope management, and they read cleanly for anyone reviewing the query. The tradeoff is that they can't be indexed, can't hold intermediate state across statements, and encourage developers to nest CTE on top of CTE until the query becomes genuinely hard to reason about.

A CTE that's referenced multiple times or that wraps a window function is fine. A five-level-deep chain of CTEs each filtering the last is usually a sign the logic should be broken into a temp table with clear checkpoints instead.

Use Cases: Which One to Pick

For staging data inside a stored procedure that will be joined, filtered, or aggregated multiple times, and where row counts could reasonably reach the thousands or more, reach for a temp table. The ability to add a targeted index and get real statistics pays for itself the moment the optimizer has to make a nontrivial join decision.

For small lookup sets — configuration values, a short list of IDs passed in in bulk, a handful of rows used once and discarded — a table variable is a reasonable, low-ceremony choice. It avoids the small overhead of explicit DROP TABLE cleanup and keeps scope tightly bound to the procedure, which some teams prefer for readability.

For a single self-contained query, especially one that benefits from readable, named intermediate steps or that requires actual recursion (hierarchies, bill-of-materials structures, graph traversal within reasonable depth), a CTE is the right tool. Don't force a CTE to do a temp table's job just to avoid an extra few lines of CREATE TABLE syntax — if the same intermediate result needs to be queried multiple times or across statements, a CTE isn't built for that.

One area worth a specific mention: memory-optimized table variables, which use the In-Memory OLTP engine (a memory-optimized data storage engine distinct from regular tempdb-backed structures) rather than tempdb.

SQL Server, Azure SQL Database, and Azure SQL Managed Instance all share the same In-Memory OLTP implementation, but availability depends on your tier. On Azure SQL Database, it's supported on the Premium and Business Critical tiers; the Hyperscale tier also supports a subset of In-Memory OLTP objects, including memory-optimized table types and table variables, but not durable or non-durable memory-optimized tables. On Azure SQL Managed Instance, it's Business Critical only — the General Purpose tier doesn't support it at all.

These can meaningfully reduce memory allocation and latch contention in very high-throughput OLTP scenarios with extremely short-lived, small result sets. They come with real constraints, though: they require a memory-optimized filegroup to already be configured on the database, their structure can't be altered once declared, and they're a genuinely different commitment than a plain @table declaration. Reserve them for cases where tempdb contention on table variables is a measured, confirmed bottleneck in your environment, and validate the specific behavior against Microsoft's current documentation for your platform and tier before committing to it.

Limitations: What Each One Doesn't Do Well

Temp tables carry real overhead: explicit creation and cleanup, potential tempdb I/O for larger sets, and possible recompilation of the surrounding stored procedure if row counts vary wildly between executions, since the optimizer may decide the cached plan no longer fits.

They also don't automatically disappear at the end of a batch the way a table variable does. Inside a stored procedure this rarely matters, since the procedure-scoped temp table is dropped when the procedure finishes — but a #temp table created in an ad-hoc script or kept alive in a long-running session won't clean itself up, so it can linger for as long as that session stays open if you forget to drop it explicitly.

Table variables can't hold large volumes of data efficiently without solid cardinality estimation behind them, and don't support ALTER TABLE. They also carry a specific parallelism restriction: any statement that modifies a table variable — an INSERT, UPDATE, or DELETE against it — is forced to run with a serial execution plan for that statement, even on a server with plenty of cores available.

That restriction covers the whole statement, not just the write: an INSERT INTO @table SELECT ... forces the entire query serial, including the SELECT side gathering the source data, even if that source query would normally benefit from parallelism on its own. Statements that only read from a table variable aren't subject to that restriction and can go parallel like any other query.

For a large staging insert, that serial requirement alone can make a table variable noticeably slower than the equivalent #temp table. They're also easy to overuse out of habit once a developer learns "table variables are lighter," which isn't reliably true once volume grows.

CTEs can't be indexed, can't be reused across statements, and offer the optimizer no independent statistics — they inherit whatever estimation quality the base tables provide. Recursive CTEs, while powerful, can also produce surprisingly expensive plans on deep or wide hierarchies if MAXRECURSION isn't set thoughtfully and the anchor/recursive member logic isn't tightly filtered.

Conclusion

The temp table vs table variable vs CTE SQL Server decision isn't really about which one is "faster" in the abstract — it's about which one gives the optimizer the information it needs for your specific row counts and access pattern. Use temp tables when you need real statistics and indexing for meaningful data volumes, use table variables for small, short-lived sets where scope simplicity matters more than estimation accuracy, and use CTEs for single-statement readability or genuine recursion. You now have a concrete way to check which one is right for a given procedure: look at the actual execution plan, compare estimated versus actual row counts, and let that evidence — not habit — drive the choice. As a next step, run SET STATISTICS IO, TIME ON against your own slow stored procedure and compare the plan under each of the three approaches before committing to a rewrite.