When working with intermediate data storage in SQL Server, you'll often face a critical decision: should you use a temporary table, table variable, or Common Table Expression (CTE)? Each option has distinct advantages and performance characteristics that can significantly impact your query execution.
In this comprehensive guide, I'll walk you through the differences between temporary tables, table variables, and CTEs, showing you exactly when to use each one with real-world examples from the WideWorldImporters database.
Understanding Your Three Options for Temporary Data Storage
Before diving into comparisons, let's establish what each option actually is and how it works in SQL Server.
What Are Temporary Tables?
Temporary tables are physical tables created in the tempdb database. They're prefixed with a single hash (#) for local temporary tables or double hash (##) for global temporary tables. These tables behave much like regular tables but exist only for the duration of your session or until explicitly dropped.
What Are Table Variables?
Table variables are special variable types that hold a result set, declared using the DECLARE statement with a table-valued data type. They also reside in tempdb but are treated differently by SQL Server's query optimizer compared to temporary tables.
What Are Common Table Expressions (CTEs)?
CTEs are named temporary result sets defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or MERGE statement. They exist only during query execution and cannot be reused in subsequent queries.
Step-by-Step Comparison with WideWorldImporters Examples
Let me show you practical examples using the WideWorldImporters sample database to illustrate when each option shines.
Example 1: Using a Temporary Table for Complex Multi-Step Processing
Temporary tables excel when you need to perform multiple operations on intermediate results or when working with large datasets that benefit from statistics and indexing.
-- Create a temporary table to store high-value customers
CREATE TABLE #HighValueCustomers (
CustomerID INT PRIMARY KEY,
CustomerName NVARCHAR(100),
TotalOrderValue DECIMAL(18, 2),
OrderCount INT
);
-- Populate the temporary table
INSERT INTO #HighValueCustomers (CustomerID, CustomerName, TotalOrderValue, OrderCount)
SELECT
c.CustomerID,
c.CustomerName,
SUM(ol.Quantity * ol.UnitPrice) AS TotalOrderValue,
COUNT(DISTINCT o.OrderID) AS OrderCount
FROM Sales.Customers c
INNER JOIN Sales.Orders o ON c.CustomerID = o.CustomerID
INNER JOIN Sales.OrderLines ol ON o.OrderID = ol.OrderID
WHERE o.OrderDate >= '2016-01-01'
GROUP BY c.CustomerID, c.CustomerName
HAVING SUM(ol.Quantity * ol.UnitPrice) > 50000;
-- Create an index for better performance on subsequent queries
CREATE NONCLUSTERED INDEX IX_TotalOrderValue ON #HighValueCustomers(TotalOrderValue);
-- Now perform multiple operations using the temp table
SELECT
hvc.CustomerName,
hvc.TotalOrderValue,
p.FullName AS SalespersonName,
COUNT(DISTINCT o.OrderID) AS RecentOrders
FROM #HighValueCustomers hvc
INNER JOIN Sales.Orders o ON hvc.CustomerID = o.CustomerID
INNER JOIN Application.People p ON o.SalespersonPersonID = p.PersonID
WHERE o.OrderDate >= '2016-05-01'
GROUP BY hvc.CustomerName, hvc.TotalOrderValue, p.FullName
ORDER BY hvc.TotalOrderValue DESC;
-- Clean up
DROP TABLE #HighValueCustomers;Key advantages here: The temporary table allows us to create an index, which improves performance on the second query. SQL Server also creates statistics on temporary tables, helping the query optimizer make better decisions.
Example 2: Using a Table Variable for Small Result Sets
Table variables work best for small to moderate datasets where you need quick, simple operations without the overhead of statistics or explicit cleanup.
-- Declare a table variable to hold stock items needing reorder
DECLARE @StockItemsToReorder TABLE (
StockItemID INT,
StockItemName NVARCHAR(100),
QuantityOnHand INT,
ReorderLevel INT,
SupplierID INT
);
-- Populate the table variable
INSERT INTO @StockItemsToReorder
SELECT
si.StockItemID,
si.StockItemName,
sih.QuantityOnHand,
sih.ReorderLevel,
si.SupplierID
FROM Warehouse.StockItems si
INNER JOIN Warehouse.StockItemHoldings sih ON si.StockItemID = sih.StockItemID
WHERE sih.QuantityOnHand <= sih.ReorderLevel
AND si.IsChillerStock = 0;
-- Use the table variable to generate purchase recommendations
SELECT
str.StockItemName,
str.QuantityOnHand,
str.ReorderLevel,
s.SupplierName,
str.ReorderLevel * 2 AS RecommendedOrderQuantity
FROM @StockItemsToReorder str
INNER JOIN Purchasing.Suppliers s ON str.SupplierID = s.SupplierID
ORDER BY str.SupplierID, str.StockItemName;Key advantages here: Table variables don't require explicit cleanup and are automatically deallocated when they go out of scope. For smaller result sets like this inventory check, they're perfect.
Example 3: Using a CTE for Readable, Single-Use Queries
CTEs are ideal when you need to simplify complex queries, improve readability, or perform recursive operations—all within a single statement.
-- Use a CTE to find customers and their most recent order details
WITH CustomerOrderSummary AS (
SELECT
c.CustomerID,
c.CustomerName,
c.CreditLimit,
COUNT(o.OrderID) AS TotalOrders,
MAX(o.OrderDate) AS LastOrderDate,
SUM(ol.Quantity * ol.UnitPrice) AS TotalSpent
FROM Sales.Customers c
INNER JOIN Sales.Orders o ON c.CustomerID = o.CustomerID
INNER JOIN Sales.OrderLines ol ON o.OrderID = ol.OrderID
WHERE o.OrderDate >= '2015-01-01'
GROUP BY c.CustomerID, c.CustomerName, c.CreditLimit
),
CustomerRanking AS (
SELECT
CustomerID,
CustomerName,
TotalOrders,
LastOrderDate,
TotalSpent,
CreditLimit,
RANK() OVER (ORDER BY TotalSpent DESC) AS SpendingRank
FROM CustomerOrderSummary
)
SELECT
cr.CustomerName,
cr.TotalOrders,
cr.LastOrderDate,
cr.TotalSpent,
cr.CreditLimit,
cr.SpendingRank,
CASE
WHEN cr.TotalSpent > cr.CreditLimit * 0.8 THEN 'High Credit Usage'
WHEN cr.TotalSpent > cr.CreditLimit * 0.5 THEN 'Moderate Credit Usage'
ELSE 'Low Credit Usage'
END AS CreditUtilization
FROM CustomerRanking cr
WHERE cr.SpendingRank <= 20
ORDER BY cr.SpendingRank;Key advantages here: The CTE makes this complex query much more readable by breaking it into logical sections. We can reference CustomerOrderSummary in the second CTE, creating a clean, maintainable query structure.
Performance Characteristics: What You Need to Know
Understanding the performance implications of each option is crucial for intermediate SQL developers.
Statistics and Query Optimization
Temporary tables maintain column statistics, which helps SQL Server's query optimizer create efficient execution plans. When you're working with thousands or millions of rows, these statistics can dramatically improve performance.
Table variables don't maintain statistics (prior to SQL Server 2019, and limited statistics even after). The optimizer assumes a table variable contains exactly one row, which can lead to poor execution plans for larger datasets.
CTEs don't create separate objects—they're essentially inline views. The optimizer treats them as part of the main query, which means they're re-evaluated each time they're referenced in the same statement.
Transaction and Locking Behavior
Here's a critical difference many intermediate developers miss:
Temporary tables participate fully in transactions. If you roll back a transaction, changes to temporary tables are also rolled back.
Table variables have minimal transaction logging. Changes to table variables are NOT rolled back if the transaction is rolled back—only the INSERT, UPDATE, or DELETE statement itself is rolled back.
CTEs follow the transaction behavior of the statement they're part of.
-- Demonstrating transaction behavior
BEGIN TRANSACTION;
-- Temporary table changes will roll back
CREATE TABLE #TransactionTest (ID INT, Value NVARCHAR(50));
INSERT INTO #TransactionTest VALUES (1, 'Test');
-- Table variable changes will NOT roll back completely
DECLARE @VariableTest TABLE (ID INT, Value NVARCHAR(50));
INSERT INTO @VariableTest VALUES (1, 'Test');
ROLLBACK TRANSACTION;
-- #TransactionTest is now empty (rollback worked)
-- @VariableTest still contains the row (rollback didn't affect it)Scope and Lifetime
Temporary tables exist until explicitly dropped or until the session ends. They're visible to nested stored procedures and dynamic SQL within the same session.
Table variables exist only within the batch or stored procedure where they're declared. They cannot be referenced in dynamic SQL.
CTEs exist only for the duration of the single statement in which they're defined.
Common Mistakes and How to Avoid Them
Through years of SQL Server development, I've seen these mistakes repeatedly—and made some myself.
Mistake 1: Using Table Variables for Large Datasets
Many developers default to table variables thinking they're "lighter weight," but for datasets with more than a few hundred rows, the lack of statistics can cripple performance.
Solution: Use temporary tables when your intermediate result set exceeds approximately 100-500 rows, or when you're uncertain about the size.
Mistake 2: Not Cleaning Up Temporary Tables
While temporary tables are automatically dropped at session end, explicitly dropping them in long-running scripts prevents tempdb bloat.
Solution: Always include DROP TABLE #TempTableName at the end of your scripts, especially in stored procedures that might be called repeatedly.
Mistake 3: Trying to Reuse CTEs Across Multiple Statements
CTEs cannot be referenced in subsequent queries—they're not persisted like temporary tables or table variables.
-- This WILL NOT work
WITH CustomerCTE AS (
SELECT CustomerID, CustomerName FROM Sales.Customers
)
SELECT * FROM CustomerCTE; -- This works
SELECT * FROM CustomerCTE; -- ERROR: Invalid object name 'CustomerCTE'Solution: If you need to reference the same result set multiple times across different statements, use a temporary table or table variable instead.
Mistake 4: Over-Indexing Temporary Tables
Adding indexes to temporary tables can improve performance, but creating too many indexes on small temp tables adds unnecessary overhead.
Solution: Index temporary tables only when they contain significant data (thousands of rows) and you're performing multiple queries against them.
When to Use Each Option: A Decision Framework
Here's my practical decision-making framework developed from real-world projects:
Choose Temporary Tables when:
- Working with large result sets (>1000 rows)
- Performing multiple operations on the intermediate data
- You need to create indexes for performance
- The data needs to be accessible in nested procedures or dynamic SQL
- Transaction rollback behavior is important
Choose Table Variables when:
- Working with small result sets (<100-500 rows)
- Performing simple, single-operation transformations
- You want minimal transaction log overhead
- The scope can be limited to a single batch or procedure
- You're in a stored procedure and want automatic cleanup
Choose CTEs when:
- You need to improve query readability
- Working with recursive queries (CTEs are your only option here)
- The result is used only once in a single statement
- You want to avoid creating physical objects in
tempdb - Performance is adequate without statistics or indexes
Summary and Key Takeaways
Choosing between temporary tables, table variables, and CTEs isn't about finding the "best" option—it's about selecting the right tool for your specific scenario.
Remember these core principles:
- Temporary tables offer the most flexibility and performance optimization options for larger datasets
- Table variables provide lightweight storage for small, simple result sets with automatic cleanup
- CTEs excel at making complex queries readable and handling recursive operations
- Statistics matter—they can make or break query performance
- Always consider the size of your intermediate result set when making your choice
- Test your specific scenario—performance can vary based on data distribution and server configuration
As you continue developing your SQL Server skills, you'll develop an intuition for which approach fits each situation. Start with the decision framework I've provided, measure your query performance, and adjust as needed.
Frequently Asked Questions
Q: Can I create indexes on table variables like I can with temporary tables?
A: Starting with SQL Server 2014, you can define primary keys, unique constraints, and indexes inline with the table variable declaration, but you cannot add them after declaration using CREATE INDEX. However, table variables still don't maintain the same level of statistics as temporary tables, which limits optimizer effectiveness.
Q: Do CTEs improve performance compared to subqueries?
A: CTEs don't inherently improve performance—they're primarily a readability and maintainability feature. The execution plan for a CTE and an equivalent subquery is typically identical. However, better readability can indirectly improve performance by making optimization opportunities more obvious to developers.
Q: Will using temporary tables instead of table variables cause tempdb contention issues?
A: Both temporary tables and table variables use tempdb, so switching from table variables to temporary tables doesn't create tempdb usage where none existed before. However, temporary tables do generate more metadata operations. In high-concurrency environments, proper tempdb configuration (multiple data files, appropriate sizing) matters more than your choice between these objects.