SQL Temporary Tables vs Table Variables vs CTEs

🔹 Why This Topic Is Important

In complex SQL workflows, we often need to store intermediate query results temporarily — for reuse, performance optimization, or readability. SQL offers multiple ways to handle this: temporary tables, table variables, and common table expressions (CTEs). Each serves a specific use case — knowing when to use which one is crucial for performance and scalability.

⚙️ 1️⃣ Temporary Tables

A temporary table is a physical table created in tempdb that exists only for the session (or scope) in which it’s created.

CREATE TABLE #EmployeeTemp (
    EmpID INT,
    EmpName VARCHAR(100),
    Department VARCHAR(50)
);
INSERT INTO #EmployeeTemp VALUES (101, 'Suresh', 'QA'), (102, 'Sharmili', 'Dev');
SELECT * FROM #EmployeeTemp;

✅ Exists only for your session.
✅ Deleted automatically after the session ends.

Property	Description
Prefix	`#` (local) or `##` (global)
Scope	Local to session or connection
Stored In	`tempdb`
Supports Indexes	Yes
Performance	Faster for large datasets
Usage	Reused across multiple queries or stored procedures

-- Local Temporary Table
CREATE TABLE #Temp1 (id INT);

-- Global Temporary Table (accessible by other sessions)
CREATE TABLE ##TempGlobal (id INT);

⚙️ 2️⃣ Table Variables

A table variable is an in-memory table-like structure declared using DECLARE. They are lightweight and ideal for small datasets or procedural logic.

DECLARE @Employee TABLE (
    EmpID INT,
    EmpName VARCHAR(100)
);
INSERT INTO @Employee VALUES (201, 'Rahul'), (202, 'Neha');
SELECT * FROM @Employee;

✅ Exists only in the batch, stored procedure, or function scope.
✅ Minimal transaction logging.
⚠️ Does not support full indexing like temporary tables.

Property	Description
Scope	Batch / Stored Procedure
Performance	Good for small data; slower for large data
Transaction Logging	Minimal
Indexes	Only primary key or unique constraints
Stored In	Memory (tempdb if needed)
Recompilation	Does not trigger re-optimization like temp tables

⚙️ 3️⃣ Common Table Expressions (CTEs)

A CTE is a temporary named query result that exists only within the execution scope of a single SQL statement. CTEs improve readability and recursion, but are not stored physically.

WITH DepartmentCount AS (
    SELECT Department, COUNT(*) AS EmpCount
    FROM Employees
    GROUP BY Department
)
SELECT *
FROM DepartmentCount
WHERE EmpCount > 5;

✅ Defined using the WITH keyword.
✅ Exists only during query execution.
✅ Perfect for recursive or multi-level hierarchical queries.

🔁 Recursive CTE Example

WITH EmployeeHierarchy AS (
    SELECT EmpID, ManagerID, EmpName
    FROM Employees
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.EmpID, e.ManagerID, e.EmpName
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmpID
)
SELECT *
FROM EmployeeHierarchy;

Recursive CTEs are ideal for organizational trees, category hierarchies, and traversing parent-child relationships.

⚙️ 4️⃣ Comparison Table

Feature	Temporary Table	Table Variable	CTE
Scope	Session	Batch / Procedure	Single Query
Stored In	`tempdb`	Memory / `tempdb`	Memory (query only)
Performance	Best for large datasets	Best for small datasets	Great for readable transformations
Indexes	Yes	Limited	No
Reusability	Yes (within session)	Yes (within scope)	No
Transactions	Fully supported	Partially supported	N/A
Recursive Query Support	No	No	✅ Yes
Primary Use Case	ETL, intermediate result caching	Procedural logic	Readability, recursion

⚙️ 5️⃣ Choosing the Right One

Scenario	Best Option
Store and reuse data across multiple queries	Temporary Table
Small, fast in-memory dataset	Table Variable
Simplify long complex query or recursion	CTE
Need indexing and statistics	Temporary Table
Inside stored procedure	Table Variable

⚙️ 6️⃣ Real-World Example

Scenario: Fetch top-performing employees per department and reuse in multiple queries.

-- Step 1: Store result in a temporary table
SELECT * INTO #TopPerformers
FROM Employees
WHERE Rating > 4.5;

-- Step 2: Use the same temp table in next queries
SELECT Department, COUNT(*)
FROM #TopPerformers
GROUP BY Department;

-- Step 3: Clean up
DROP TABLE #TopPerformers;

Temporary tables excel when intermediate results must be reused across several statements in the same session.

💡 Interview Tip

“Temporary tables, table variables, and CTEs all store intermediate data differently. For large reusable data, use temporary tables; for small scoped logic, table variables; and for readable one-time transformations, CTEs.”

← Previous All SQL Articles Next →

← Back to Articles | 🏠 Back to Homepage