CTE vs #Temp Table trong SQL Server: Khác nhau thế nào và khi nào nên dùng?

# CTE vs #Temp Table trong SQL Server: Khác nhau thế nào và khi nào nên dùng?

Khi viết query phức tạp trong SQL Server, hai công cụ rất hay gặp là:

“`sql
CTE
“`

và:

“`sql
#TempTable
“`

Nhìn qua thì cả hai đều có thể giúp chia nhỏ query và xử lý dữ liệu trung gian. Nhưng về bản chất, cách hoạt động và use case của chúng khác nhau khá nhiều.

Một câu hỏi interview phổ biến là:

> Khi nào nên dùng CTE, khi nào nên dùng #Temp Table?

Và câu hỏi phụ thường đi kèm:

> Khi nào #Temp Table bị xóa?

—

# 1. CTE là gì?

CTE là viết tắt của:

“`text
Common Table Expression
“`

Nó là một tập kết quả tạm thời được định nghĩa trong scope của một câu SQL.

Ví dụ:

“`sql
WITH ActiveUsers AS
(
SELECT
Id,
Name,
Email
FROM Users
WHERE IsActive = 1
)
SELECT *
FROM ActiveUsers;
“`

Bạn có thể hiểu CTE giống như:

“`text
đặt tên cho một query tạm thời
“`

để query phía sau dễ đọc hơn.

—

# 2. CTE có tạo bảng thật trong tempdb không?

Không nên hiểu CTE là một temporary table.

CTE chủ yếu là một cách biểu diễn query logic.

Ví dụ:

“`sql
WITH ActiveUsers AS
(
SELECT *
FROM Users
WHERE IsActive = 1
)
SELECT *
FROM ActiveUsers;
“`

Optimizer có thể inline logic này vào execution plan.

Nó không có lifecycle giống:

“`sql
#TempTable
“`

và bạn cũng không thể:

“`sql
DROP CTE
“`

—

# 3. Scope của CTE

CTE chỉ tồn tại cho câu statement ngay sau nó.

Ví dụ:

“`sql
WITH ActiveUsers AS
(
SELECT *
FROM Users
WHERE IsActive = 1
)
SELECT *
FROM ActiveUsers;
“`

Sau câu này:

“`sql
SELECT *
FROM ActiveUsers;
“`

sẽ lỗi.

Bởi vì CTE đã hết scope.

—

# 4. #Temp Table là gì?

Temporary table là bảng tạm được tạo trong:

“`text
tempdb
“`

Ví dụ:

“`sql
CREATE TABLE #ActiveUsers
(
Id INT,
Name NVARCHAR(200)
);
“`

Insert dữ liệu:

“`sql
INSERT INTO #ActiveUsers
SELECT
Id,
Name
FROM Users
WHERE IsActive = 1;
“`

Sau đó có thể query nhiều lần:

“`sql
SELECT *
FROM #ActiveUsers;

SELECT COUNT(*)
FROM #ActiveUsers;
“`

Đây là khác biệt rất lớn so với CTE.

—

# 5. Điểm khác nhau lớn nhất

Có thể nhớ như sau:

“`text
CTE
=
query expression

#TempTable
=
temporary physical table
“`

CTE phù hợp để:

“`text
làm query dễ đọc
chia nhỏ logic
recursive query
“`

Temp table phù hợp để:

“`text
lưu intermediate result
reuse nhiều lần
index dữ liệu tạm
giảm workload cho query sau
“`

—

# 6. So sánh nhanh

—

# 7. Khi nào nên dùng CTE?

CTE rất phù hợp khi mục tiêu chính là:

“`text
readability
“`

Ví dụ query phức tạp:

“`sql
SELECT …
FROM …
JOIN …
WHERE …
GROUP BY …
“`

có thể được chia:

“`sql
WITH PaidOrders AS
(
SELECT *
FROM Orders
WHERE Status = ‘Paid’
),
LargeOrders AS
(
SELECT *
FROM PaidOrders
WHERE TotalAmount > 1000
)
SELECT *
FROM LargeOrders;
“`

Code dễ đọc hơn nhiều.

—

# 8. CTE rất phù hợp cho recursive query

Đây là một trong những use case đặc trưng nhất.

Ví dụ organizational hierarchy:

“`text
CEO
├── Manager A
│ ├── Developer 1
│ └── Developer 2
└── Manager B
“`

Recursive CTE:

“`sql
WITH EmployeeHierarchy AS
(
SELECT
Id,
ManagerId,
Name,
0 AS Level
FROM Employees
WHERE ManagerId IS NULL

UNION ALL

SELECT
e.Id,
e.ManagerId,
e.Name,
h.Level + 1
FROM Employees e
INNER JOIN EmployeeHierarchy h
ON e.ManagerId = h.Id
)
SELECT *
FROM EmployeeHierarchy;
“`

—

# 9. CTE có luôn chạy một lần không?

Không nên nghĩ như vậy.

Ví dụ:

“`sql
WITH ExpensiveData AS
(
SELECT …
FROM LargeTable
WHERE …
)
SELECT *
FROM ExpensiveData a
JOIN ExpensiveData b
ON a.Id = b.Id;
“`

Không có guarantee rằng SQL Server sẽ materialize CTE một lần rồi reuse.

Vì vậy:

> CTE không phải cache.

—

# 10. Khi nào #Temp Table tốt hơn?

Giả sử bạn có một query nặng:

“`sql
SELECT …
FROM Orders
JOIN OrderItems
JOIN Users
JOIN Payments
WHERE …
“`

và kết quả trung gian này được dùng nhiều lần.

Có thể materialize:

“`sql
SELECT …
INTO #FilteredOrders
FROM Orders
…
WHERE …;
“`

Sau đó:

“`sql
SELECT *
FROM #FilteredOrders;

SELECT COUNT(*)
FROM #FilteredOrders;

SELECT UserId, SUM(Amount)
FROM #FilteredOrders
GROUP BY UserId;
“`

Trong case này temp table thường phù hợp hơn.

—

# 11. #Temp Table có thể tạo index

Đây là advantage rất lớn.

Ví dụ:

“`sql
CREATE TABLE #FilteredOrders
(
Id BIGINT,
UserId BIGINT,
CreatedAt DATETIME2,
Amount DECIMAL(18,2)
);
“`

Sau đó:

“`sql
CREATE INDEX IX_FilteredOrders_UserId
ON #FilteredOrders(UserId);
“`

Nếu subsequent query filter nhiều theo `UserId`, index có thể cải thiện performance đáng kể.

CTE không cho bạn trực tiếp làm việc này.

—

# 12. #Temp Table có statistics

SQL Server có thể tạo statistics cho temp table.

Điều này giúp optimizer estimate row count tốt hơn.

Ví dụ:

“`text
#TempTable có 10 rows
“`

khác hoàn toàn:

“`text
#TempTable có 10 triệu rows
“`

Optimizer có thể chọn join strategy khác nhau dựa trên cardinality estimate.

—

# 13. Khi nào CTE có thể chậm hơn #Temp Table?

Giả sử logic trung gian rất phức tạp:

“`sql
WITH ComplexData AS
(
SELECT …
FROM A
JOIN B
JOIN C
JOIN D
)
SELECT …
FROM ComplexData
JOIN …
“`

Nếu optimizer estimate sai số lượng rows ở intermediate stage, toàn bộ plan phía sau có thể không tốt.

Một approach khác:

“`sql
SELECT …
INTO #ComplexData
FROM A
JOIN B
JOIN C
JOIN D;
“`

Sau khi materialize, SQL Server có statistics rõ hơn cho bước tiếp theo.

—

# 14. Nhưng đừng dùng #Temp Table mọi nơi

Temp table cũng có cost.

Bạn phải:

“`text
create
write
read
drop
“`

dữ liệu trong tempdb.

Nếu chỉ có một query đơn giản thì tạo temp table có thể làm code phức tạp hơn và thêm IO không cần thiết.

—

# 15. Khi nào #Temp Table bị xóa?

Một local temp table có tên:

“`sql
#TempTable
“`

thường tồn tại trong scope của connection/session tạo ra nó.

Ví dụ:

“`sql
CREATE TABLE #Users
(
Id INT
);
“`

Khi connection/session kết thúc, SQL Server tự động xóa temp table.

—

# 16. Có cần DROP #TempTable không?

Không bắt buộc trong mọi trường hợp.

Ví dụ:

“`sql
CREATE TABLE #Users
(
Id INT
);

SELECT *
FROM #Users;
“`

Khi session đóng, `#Users` sẽ tự bị xóa.

Nhưng có thể chủ động:

“`sql
DROP TABLE #Users;
“`

—

# 17. Khi nào nên DROP temp table thủ công?

Nếu procedure hoặc batch dài và bạn không còn cần temp table nữa, nên drop sớm.

Ví dụ:

“`sql
CREATE TABLE #LargeResult (…);

— processing

DROP TABLE #LargeResult;

— còn rất nhiều logic phía sau
“`

Lợi ích:

“`text
release tempdb resources sớm hơn
“`

và lifecycle của dữ liệu rõ ràng hơn.

—

# 18. DROP TABLE IF EXISTS

Có thể dùng:

“`sql
DROP TABLE IF EXISTS #Users;
“`

Pattern phổ biến:

“`sql
DROP TABLE IF EXISTS #Users;

CREATE TABLE #Users
(
Id INT
);
“`

Hữu ích khi chạy script nhiều lần trong cùng session.

—

# 19. Temp Table trong Stored Procedure

Ví dụ:

“`sql
CREATE PROCEDURE GetReport
AS
BEGIN

CREATE TABLE #Data
(
Id INT
);

INSERT INTO #Data
SELECT Id
FROM Users;

SELECT *
FROM #Data;

END
“`

Khi stored procedure kết thúc, local temp table được tạo trong procedure thường được cleanup.

Bạn không nhất thiết phải:

“`sql
DROP TABLE #Data;
“`

nhưng vẫn có thể làm nếu muốn explicit cleanup.

—

# 20. Local Temp Table vs Global Temp Table

## Local temp table

“`sql
#Users
“`

Chỉ accessible theo scope/session phù hợp.

## Global temp table

“`sql
##Users
“`

Có thể được nhiều session nhìn thấy.

Ví dụ:

“`sql
CREATE TABLE ##GlobalUsers
(
Id INT
);
“`

Global temp table ít được dùng hơn trong application backend thông thường.

—

# 21. CTE vs Subquery

CTE:

“`sql
WITH ActiveUsers AS
(
SELECT *
FROM Users
WHERE IsActive = 1
)
SELECT *
FROM ActiveUsers
WHERE CreatedAt >= @date;
“`

Subquery:

“`sql
SELECT *
FROM
(
SELECT *
FROM Users
WHERE IsActive = 1
) u
WHERE CreatedAt >= @date;
“`

Trong nhiều trường hợp optimizer có thể tạo execution plan tương tự.

CTE thường có lợi thế về:

“`text
readability
“`

hơn là performance magic.

—

# 22. CTE vs Table Variable

Ngoài CTE và temp table còn có:

“`sql
DECLARE @Users TABLE
(
Id INT
);
“`

Đây là table variable.

Nhưng đừng đánh đồng `@Table` với `#TempTable`.

Chúng có khác biệt về:

“`text
statistics
cardinality estimation
scope
indexing options
optimization behavior
“`

—

# 23. Một ví dụ thực tế

Giả sử cần tạo report:

“`text
Users
Orders
Payments
Rewards
“`

Nếu viết một lần:

“`sql
WITH ReportData AS
(
SELECT …
)
SELECT …
FROM ReportData;
“`

thì CTE rất hợp lý.

Nhưng nếu cùng dataset được dùng để tạo:

“`text
Summary
Top Users
Revenue Chart
Reward Statistics
“`

thì có thể tốt hơn khi:

“`sql
SELECT …
INTO #ReportData
FROM …;
“`

sau đó reuse:

“`sql
SELECT COUNT(*)
FROM #ReportData;

SELECT TOP 10 *
FROM #ReportData;

SELECT SUM(Revenue)
FROM #ReportData;

SELECT RewardType, COUNT(*)
FROM #ReportData
GROUP BY RewardType;
“`

—

# 24. Cách quyết định

Mình thường bắt đầu bằng câu hỏi:

“`text
Intermediate result có được reuse không?
“`

Nếu không, thường ưu tiên CTE.

Nếu có, xem xét `#TempTable`.

Tiếp theo hỏi:

“`text
Data có lớn không?
Need index?
Need statistics?
Would materialization improve the next execution plan?
“`

Nếu có, `#TempTable` thường đáng cân nhắc.

—

# 25. Decision tree

“`text
Need recursive query?
|
Yes
↓
CTE
“`

Nếu không:

—

# 26. Interview Question: CTE vs Temp Table?

> A CTE is mainly a query expression scoped to a single statement, while a temp table materializes intermediate data in tempdb and can be reused across multiple statements. I prefer CTEs for readability, simple transformations, and recursive queries. I consider temp tables when the intermediate result is large, reused multiple times, needs indexes, or when materializing the result helps SQL Server produce a better execution plan.

—

# 27. Interview Question: Does a CTE improve performance?

> Not necessarily. A CTE mainly improves query structure and readability. SQL Server can inline it into the execution plan, so it should not be treated as cached or materialized data. Performance depends on the generated execution plan.

—

# 28. Interview Question: When is a temp table deleted?

> A local temp table exists in tempdb and is normally dropped automatically when the session that created it ends. If it is created inside a stored procedure, it is cleaned up when its scope ends. I can also explicitly drop it earlier with DROP TABLE when I no longer need it.

—

# 29. Interview Question: Why can a temp table improve performance?

> A temp table can improve performance when I materialize an expensive intermediate result, reuse it several times, add indexes to it, or give the optimizer better statistics and cardinality information for subsequent queries. But it also adds tempdb IO and write cost, so I don’t use it by default.

—

# 30. Những lỗi phổ biến

Không nên nghĩ:

“`text
CTE always faster
“`

hoặc:

“`text
Temp table always faster
“`

Cũng không nên:

“`text
replace every subquery with CTE
“`

hay:

“`text
create temp table for every step
“`

Điều cần nhìn là:

“`text
Execution Plan
Row Count
Reuse
Statistics
Indexing
tempdb cost
“`

—

# Kết luận

Điểm khác biệt quan trọng nhất:

“`text
CTE
=
logical query expression
=
single statement
=
readability / recursion
“`

Trong khi:

“`text
#TempTable
=
materialized intermediate table
=
tempdb
=
reuse / index / statistics
“`

Rule đơn giản:

> **Nếu chỉ cần chia query cho dễ đọc, hãy bắt đầu với CTE. Nếu cần lưu một intermediate result để reuse, index hoặc giúp optimizer xử lý query phức tạp tốt hơn, hãy xem xét #Temp Table.**

Và với câu hỏi:

> Khi nào temp table bị xóa?

Hãy nhớ:

“`text
#TempTable
→ session/scope kết thúc
→ SQL Server tự cleanup
“`

Nhưng nếu không còn sử dụng trong một batch hoặc procedure dài:

“`sql
DROP TABLE IF EXISTS #TempTable;
“`

là một lựa chọn tốt để giải phóng resource sớm và làm lifecycle của dữ liệu rõ ràng hơn.