Difference between CTE and Temp Table and Table Variable

Estimated study time: 14 minutes. A clear, side-by-side comparison to help you pick the right temporary data structure.

SQL Server gives you three common ways to hold intermediate result sets: a CTE (Common Table Expression), a Temp Table, and a Table Variable. They look similar on the surface but behave very differently under the hood.

What is a CTE?

A CTE is a named, temporary result set defined using WITH that exists only for the duration of a single query. It is not physically stored anywhere — SQL Server expands it into the query at execution time.

WITH HighEarners AS (
    SELECT EmpName, Salary
    FROM Employees
    WHERE Salary > 50000
)
SELECT * FROM HighEarners ORDER BY Salary DESC;

What is a Temp Table?

A temp table (#TempTable or ##GlobalTempTable) is a real table created in tempdb. It supports indexes, constraints, and statistics, and it persists for the life of the session or connection, not just one query.

CREATE TABLE #HighEarners (EmpName VARCHAR(100), Salary INT);
INSERT INTO #HighEarners
SELECT EmpName, Salary FROM Employees WHERE Salary > 50000;

SELECT * FROM #HighEarners;

What is a Table Variable?

A table variable (DECLARE @Table TABLE (...)) also lives in tempdb but behaves more like a variable: its scope is limited to the batch or procedure in which it's declared, and it doesn't participate in transaction rollbacks the same way a temp table does.

DECLARE @HighEarners TABLE (EmpName VARCHAR(100), Salary INT);
INSERT INTO @HighEarners
SELECT EmpName, Salary FROM Employees WHERE Salary > 50000;

SELECT * FROM @HighEarners;

Key Differences