CTE vs Temp Table vs Table Variable - What Should You Use?

SQL Server gives us three common ways to work with intermediate data:

CTE
#Temp Table
@Table Variable

They may look similar, but their purpose is different.

---

1. CTE - Organize a Query

Use a CTE when you mainly want to make a query easier to read.

WITH ActiveCustomers AS
(
    SELECT CustomerId, Name
    FROM Customers
    WHERE IsActive = 1
)
SELECT *
FROM ActiveCustomers
WHERE Country = 'USA';

Think:

"I need to structure this query."

A CTE is not automatically a temporary table. It is a query expression and does not mean the result is materialized and stored just because you used WITH.

Good for:

• Readability
• Complex queries
• Recursive queries

---

2. Temp Table - Multiple Steps or Larger Data

Use a temp table when you need to reuse intermediate data.

CREATE TABLE #ActiveCustomers
(
    CustomerId INT,
    Name VARCHAR(100),
    Country VARCHAR(50)
);

INSERT INTO #ActiveCustomers
SELECT CustomerId, Name, Country
FROM Customers
WHERE IsActive = 1;

SELECT *
FROM #ActiveCustomers
WHERE Country = 'USA';

SELECT COUNT(*)
FROM #ActiveCustomers;

Think:

Query 1
   ↓
#Temp Table
   ↓
Query 2
   ↓
Query 3

Temp tables can have statistics and indexes, which can help SQL Server make better decisions for larger or more complex intermediate datasets.

CREATE INDEX IX_ActiveCustomers_Country
ON #ActiveCustomers(Country);

---

3. Table Variable - Small Temporary Data

Use a table variable when the intermediate dataset is small and the logic is relatively simple.

DECLARE @Ids TABLE
(
    Id INT
);

INSERT INTO @Ids
VALUES (10), (20), (30);

SELECT *
FROM Customers
WHERE CustomerId IN
(
    SELECT Id
    FROM @Ids
);

Think:

"I need a small temporary data structure."

Don't say:

"Table variables always estimate 1 row."

That is an outdated rule.

Modern SQL Server versions support improvements such as table variable deferred compilation at appropriate compatibility levels.

However, table variables still don't behave exactly like temp tables, and temp tables are often preferable for larger or more complex workloads where statistics matter.

---

Quick Comparison

---

Question: Which One Would You Choose?

Scenario 1

I only need to simplify one complex query.

Answer: CTE.

Scenario 2

I have 500,000 intermediate rows and need to query them several times.

Answer: Temp table.

Why?

Large data
   +
Multiple operations
   +
Statistics / indexes
   ↓
#Temp Table

Scenario 3

I need to store 20 IDs temporarily inside a procedure.

Answer: Table variable is a reasonable choice.

---

If asked:

"CTE vs Temp Table vs Table Variable?"

A concise answer is:

CTE is mainly for structuring a query.
Temp tables are useful for reusable or larger intermediate data, especially when statistics and indexes matter.
Table variables are convenient for smaller temporary datasets.
The right choice depends on data size, reuse, query complexity, and the execution plan.

And one important rule:

Don't choose based on "which is always faster." Choose based on the workload.