# SQL Server: `DELETE` và `TRUNCATE` khác nhau như thế nào? Liên hệ với Trigger, Identity và Transaction
Trong SQL Server, `DELETE` và `TRUNCATE TABLE` đều được dùng để xóa dữ liệu trong bảng.
Nhìn qua thì có vẻ giống nhau:
“`sql id=”dela01″
DELETE FROM Users;
“`
và:
“`sql id=”trun01″
TRUNCATE TABLE Users;
“`
đều có thể làm bảng `Users` trở về trạng thái không còn record.
Tuy nhiên, cơ chế hoạt động của hai lệnh này khác nhau khá nhiều.
Sự khác biệt đặc biệt quan trọng khi hệ thống có:
– Trigger.
– Identity.
– Foreign Key.
– Transaction.
– Audit log.
– Bảng dữ liệu lớn.
– Production data.
Hiểu không đúng `TRUNCATE` có thể dẫn đến những bug rất khó phát hiện, nhất là khi hệ thống phụ thuộc vào trigger để ghi lịch sử.
—
# 1. DELETE là gì?
`DELETE` là lệnh dùng để xóa record khỏi bảng.
Ví dụ:
“`sql id=”del02″
DELETE FROM Users;
“`
Lệnh trên xóa toàn bộ record.
Hoặc:
“`sql id=”del03″
DELETE FROM Users
WHERE IsActive = 0;
“`
Lệnh này chỉ xóa các user không còn active.
Điểm quan trọng:
> `DELETE` hỗ trợ `WHERE`.
Do đó chúng ta có thể chọn chính xác record cần xóa.
—
# 2. DELETE hoạt động như thế nào?
Có thể hình dung `DELETE` theo logic:
“`text id=”del04″
Find rows
↓
Delete matching rows
↓
Write transaction log
↓
Fire DELETE trigger
“`
Ví dụ:
“`sql id=”del05″
DELETE FROM Users
WHERE Status = ‘inactive’;
“`
Nếu có 1.000 user inactive, SQL Server sẽ xử lý việc xóa các record phù hợp và ghi nhận thay đổi vào transaction log.
Điểm cần lưu ý:
> SQL Server trigger chạy theo statement, không phải cứ một row là trigger chạy một lần.
Chúng ta sẽ nói kỹ hơn ở phần trigger.
—
# 3. TRUNCATE TABLE là gì?
`TRUNCATE TABLE` cũng dùng để xóa dữ liệu.
Ví dụ:
“`sql id=”trun02″
TRUNCATE TABLE Users;
“`
Nhưng khác `DELETE`, `TRUNCATE` không hỗ trợ:
“`sql id=”trun03″
WHERE
“`
Ví dụ sau không hợp lệ:
“`sql id=”trun04″
TRUNCATE TABLE Users
WHERE IsActive = 0;
“`
`TRUNCATE` luôn áp dụng cho toàn bộ table.
—
# 4. TRUNCATE hoạt động khác DELETE
`TRUNCATE` không xử lý dữ liệu theo kiểu xóa từng row giống `DELETE`.
Có thể hình dung:
“`text id=”comp01″
DELETE
Row 1
Row 2
Row 3
Row 4
…
“`
Trong khi:
“`text id=”comp02″
TRUNCATE
Data pages
↓
Deallocate
“`
Nói đơn giản:
> `TRUNCATE` chủ yếu deallocate các data pages/extents mà table đang sử dụng.
Chính vì vậy, nếu table rất lớn, `TRUNCATE` thường nhanh hơn `DELETE`.
—
# 5. DELETE và TRUNCATE khác nhau về Trigger
Đây là một trong những điểm quan trọng nhất.
Giả sử có trigger:
“`sql id=”trig01″
CREATE TRIGGER trg_User_Delete
ON Users
AFTER DELETE
AS
BEGIN
INSERT INTO UserAudit
(
UserId,
Action,
CreatedAt
)
SELECT
Id,
‘DELETE’,
GETUTCDATE()
FROM deleted;
END;
“`
Nếu chạy:
“`sql id=”trig02″
DELETE FROM Users;
“`
trigger sẽ chạy.
Nhưng nếu chạy:
“`sql id=”trig03″
TRUNCATE TABLE Users;
“`
thì `DELETE trigger` không chạy.
Đây là khác biệt cực kỳ quan trọng.
—
# 6. Vì sao TRUNCATE không chạy DELETE Trigger?
Vì `TRUNCATE TABLE` không được SQL Server xử lý như một DML DELETE theo từng row.
`DELETE` tạo ra deleted rows.
Do đó trigger có thể đọc pseudo-table:
“`sql id=”trig04″
deleted
“`
Ví dụ:
“`sql id=”trig05″
SELECT *
FROM deleted;
“`
Nhưng với:
“`sql id=”trig06″
TRUNCATE TABLE Users;
“`
không có quá trình DELETE row theo cùng cách đó.
Do đó:
“`text id=”trig07″
AFTER DELETE trigger
“`
không được gọi.
—
# 7. Risk khi hệ thống phụ thuộc vào Trigger
Giả sử hệ thống dùng trigger để ghi audit.
Flow mong muốn:
“`text id=”risk01″
DELETE Users
↓
Trigger
↓
UserAudit
“`
Nếu developer dùng:
“`sql id=”risk02″
DELETE FROM Users;
“`
audit được tạo.
Nhưng nếu developer nghĩ:
> TRUNCATE cũng chỉ là xóa nhanh hơn DELETE
và chạy:
“`sql id=”risk03″
TRUNCATE TABLE Users;
“`
thì:
“`text id=”risk04″
Users cleared
↓
No DELETE Trigger
↓
No Audit History
“`
Đây có thể là bug rất nghiêm trọng.
Đặc biệt với hệ thống:
– Finance.
– Audit.
– Compliance.
– History tracking.
– Event-driven integration.
—
# 8. Trigger trong SQL Server chạy theo statement
Đây là một nuance quan trọng.
Giả sử:
“`sql id=”trig08″
DELETE FROM Users
WHERE Status = ‘inactive’;
“`
xóa 10.000 record.
Trigger không chạy kiểu:
“`text id=”trig09″
Row 1 -> trigger
Row 2 -> trigger
Row 3 -> trigger
…
“`
Mà thường:
“`text id=”trig10″
DELETE statement
↓
10,000 rows deleted
↓
Trigger runs once
“`
Sau đó pseudo-table:
“`sql id=”trig11″
deleted
“`
chứa 10.000 record.
—
# 9. Vì vậy trigger phải viết theo kiểu set-based
Không nên viết:
“`sql id=”trig12″
DECLARE @UserId INT;
SELECT @UserId = Id
FROM deleted;
“`
Nếu `deleted` có nhiều record thì bạn có thể chỉ xử lý được một giá trị.
Nên viết:
“`sql id=”trig13″
INSERT INTO UserAudit
(
UserId,
Action
)
SELECT
Id,
‘DELETE’
FROM deleted;
“`
Đây là cách đúng hơn.
Rule:
> Luôn giả định `inserted` và `deleted` có thể chứa nhiều rows.
—
# 10. DELETE và Identity
Giả sử table:
“`sql id=”ident01″
CREATE TABLE Users
(
Id INT IDENTITY(1,1),
Name NVARCHAR(100)
);
“`
Insert:
“`sql id=”ident02″
INSERT INTO Users(Name)
VALUES
(‘A’),
(‘B’),
(‘C’);
“`
Dữ liệu:
“`text id=”ident03″
Id Name
———
1 A
2 B
3 C
“`
Sau đó:
“`sql id=”ident04″
DELETE FROM Users;
“`
rồi insert:
“`sql id=”ident05″
INSERT INTO Users(Name)
VALUES (‘D’);
“`
ID tiếp theo thường là:
“`text id=”ident06″
4
“`
`DELETE` không reset identity seed.
—
# 11. TRUNCATE và Identity
Nếu thay bằng:
“`sql id=”ident07″
TRUNCATE TABLE Users;
“`
sau đó:
“`sql id=”ident08″
INSERT INTO Users(Name)
VALUES (‘D’);
“`
ID thường quay lại:
“`text id=”ident09″
1
“`
vì `TRUNCATE` reset identity về seed ban đầu.
Đây là câu hỏi phỏng vấn rất phổ biến.
—
# 12. Nếu muốn DELETE nhưng vẫn reset Identity thì sao?
Có thể dùng:
“`sql id=”ident10″
DELETE FROM Users;
DBCC CHECKIDENT (‘Users’, RESEED, 0);
“`
Sau đó insert mới:
“`sql id=”ident11″
INSERT INTO Users(Name)
VALUES (‘New User’);
“`
ID có thể bắt đầu lại từ:
“`text id=”ident12″
1
“`
Tuy nhiên không nên reset identity một cách tùy tiện trong production.
—
# 13. DELETE và Foreign Key
Giả sử:
“`text id=”fk01″
Users
↑
Orders
“`
`Orders.UserId` reference:
“`text id=”fk02″
Users.Id
“`
Nếu chạy:
“`sql id=”fk03″
DELETE FROM Users
WHERE Id = 1;
“`
mà user 1 vẫn có Orders, SQL Server có thể chặn:
“`text id=”fk04″
Foreign Key Constraint violation
“`
trừ khi foreign key được cấu hình:
“`sql id=”fk05″
ON DELETE CASCADE
“`
—
# 14. DELETE CASCADE
Ví dụ:
“`sql id=”fk06″
FOREIGN KEY (UserId)
REFERENCES Users(Id)
ON DELETE CASCADE
“`
Khi chạy:
“`sql id=”fk07″
DELETE FROM Users
WHERE Id = 1;
“`
SQL Server có thể xóa:
“`text id=”fk08″
User
↓
related Orders
“`
theo cascade rule.
—
# 15. TRUNCATE và Foreign Key
`TRUNCATE` có restriction chặt hơn.
Nếu một table đang được reference bởi foreign key, SQL Server thường không cho:
“`sql id=”fk09″
TRUNCATE TABLE Users;
“`
Bạn có thể gặp lỗi kiểu:
“`text id=”fk10″
Cannot truncate table because it is being referenced
by a FOREIGN KEY constraint.
“`
Điểm quan trọng:
> Ngay cả khi bảng child hiện không có dữ liệu, constraint vẫn có thể khiến TRUNCATE không được phép.
—
# 16. TRUNCATE không dùng ON DELETE CASCADE
Bạn không thể kỳ vọng:
“`sql id=”fk11″
TRUNCATE TABLE Users;
“`
sẽ cascade xuống:
“`text id=”fk12″
Orders
Payments
UserRoles
“`
giống `DELETE`.
`TRUNCATE` không chạy theo semantic của `ON DELETE CASCADE`.
—
# 17. Transaction Log khác nhau như thế nào?
Một misconception phổ biến:
> TRUNCATE không ghi log.
Không đúng.
`TRUNCATE` vẫn được SQL Server ghi vào transaction log.
Nhưng cách log khác `DELETE`.
—
# 18. DELETE log nhiều hơn
Ví dụ:
“`sql id=”log01″
DELETE FROM BigTable;
“`
với:
“`text id=”log02″
10,000,000 rows
“`
SQL Server phải ghi lại rất nhiều thông tin liên quan đến row deletion.
Có thể hình dung:
“`text id=”log03″
Row delete
Row delete
Row delete
…
“`
Transaction log có thể tăng rất lớn.
—
# 19. TRUNCATE log ít hơn
Với:
“`sql id=”log04″
TRUNCATE TABLE BigTable;
“`
SQL Server chủ yếu log:
“`text id=”log05″
Page/Extent deallocation
“`
thay vì từng row deletion.
Do đó:
“`text id=”log06″
Less logging
+
Less work
+
Usually faster
“`
với table lớn.
Nhưng:
> Less logging không có nghĩa là No Logging.
—
# 20. TRUNCATE có Rollback được không?
Có.
Đây là một câu phỏng vấn rất hay gặp.
Ví dụ:
“`sql id=”tran01″
BEGIN TRANSACTION;
TRUNCATE TABLE Users;
SELECT COUNT(*)
FROM Users;
ROLLBACK;
“`
Sau `ROLLBACK`, dữ liệu có thể quay lại.
Vì `TRUNCATE` vẫn tham gia transaction.
—
# 21. DELETE cũng rollback được
Ví dụ:
“`sql id=”tran02″
BEGIN TRANSACTION;
DELETE FROM Users;
ROLLBACK;
“`
Dữ liệu cũng được restore.
Vì vậy trong SQL Server:
“`text id=”tran03″
DELETE
-> rollback được
TRUNCATE
-> rollback được
“`
nếu chúng nằm trong transaction chưa commit.
—
# 22. COMMIT thì sao?
Ví dụ:
“`sql id=”tran04″
BEGIN TRANSACTION;
TRUNCATE TABLE Users;
COMMIT;
“`
Sau khi:
“`sql id=”tran05″
COMMIT
“`
thì transaction đã hoàn thành.
Bạn không thể gọi đơn giản:
“`sql id=”tran06″
ROLLBACK;
“`
để undo transaction đã commit.
—
# 23. So sánh DELETE và TRUNCATE
| Đặc điểm | DELETE | TRUNCATE TABLE |
|—|—|—|
| Xóa toàn bộ dữ liệu | Có | Có |
| Xóa một phần dữ liệu | Có | Không |
| Hỗ trợ WHERE | Có | Không |
| DELETE Trigger | Có | Không |
| Identity reset | Không | Có |
| Foreign Key restriction | Ít hơn | Chặt hơn |
| ON DELETE CASCADE | Có thể hỗ trợ | Không |
| Transaction log | Nhiều hơn | Thường ít hơn |
| Rollback trong transaction | Có | Có |
| Performance bảng lớn | Thường chậm hơn | Thường nhanh hơn |
—
# 24. DELETE có luôn chậm không?
Không.
Ví dụ:
“`sql id=”perf01″
DELETE FROM Users
WHERE Id = 100;
“`
chỉ xóa một row.
Trong trường hợp này không có lý do gì để so với `TRUNCATE`.
`TRUNCATE` chỉ có ý nghĩa khi:
“`text id=”perf02″
Clear entire table
“`
Do đó không nên nghĩ:
“`text id=”perf03″
DELETE = slow
TRUNCATE = fast
“`
một cách tuyệt đối.
Cần xem use case.
—
# 25. Khi nào nên dùng DELETE?
Dùng `DELETE` khi cần:
– `WHERE`.
– Xóa một phần dữ liệu.
– DELETE trigger.
– Audit.
– Cascade.
– Business logic liên quan đến từng row.
– Không muốn reset identity.
Ví dụ:
“`sql id=”use01″
DELETE FROM Sessions
WHERE ExpiredAt < GETUTCDATE();
```
Hoặc:
```sql id="use02"
DELETE FROM Notifications
WHERE CreatedAt < DATEADD(MONTH, -6, GETUTCDATE());
```
---
# 26. Khi nào nên dùng TRUNCATE?
`TRUNCATE` phù hợp với các table như:
```text id="use03"
Import Staging
ETL Temp Table
Development/Test data
Intermediate processing table
```
Ví dụ:
```sql id="use04"
TRUNCATE TABLE ImportStaging;
```
Sau đó load batch mới:
```text id="use05"
TRUNCATE
↓
Import latest file
↓
Process data
```
Đây là use case rất phù hợp.
---
# 27. Không nên coi TRUNCATE là DELETE nhanh hơn
Đây là một trong những takeaway quan trọng nhất.
Không nên nghĩ:
```text id="rule01"
DELETE FROM Table
```
và:
```text id="rule02"
TRUNCATE TABLE Table
```
chỉ khác nhau ở tốc độ.
Chúng có semantic khác nhau:
```text id="rule03"
DELETE
↓
Row deletion semantics
```
Trong khi:
```text id="rule04"
TRUNCATE
↓
Whole-table deallocation semantics
```
Sự khác biệt ảnh hưởng tới:
- Trigger.
- Identity.
- Foreign Key.
- Logging.
- Permission.
---
# 28. DELETE Trigger và Audit
Ví dụ một hệ thống cần lưu lịch sử user bị xóa:
```sql id="audit01"
CREATE TRIGGER trg_Users_AuditDelete
ON Users
AFTER DELETE
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO UserDeletionHistory
(
UserId,
Email,
DeletedAt
)
SELECT
Id,
Email,
GETUTCDATE()
FROM deleted;
END;
```
Nếu:
```sql id="audit02"
DELETE FROM Users
WHERE Id = 100;
```
history được tạo.
Nhưng:
```sql id="audit03"
TRUNCATE TABLE Users;
```
không gọi trigger này.
Vì vậy trước khi `TRUNCATE` production table, cần kiểm tra:
```text id="audit04"
Triggers
Foreign Keys
Audit Requirements
Downstream Processes
```
---
# 29. DELETE và soft delete
Trong nhiều production system, thậm chí không nên dùng hard DELETE.
Ví dụ thay vì:
```sql id="soft01"
DELETE FROM Users
WHERE Id = 100;
```
có thể:
```sql id="soft02"
UPDATE Users
SET DeletedAt = GETUTCDATE()
WHERE Id = 100;
```
Sau đó query:
```sql id="soft03"
SELECT *
FROM Users
WHERE DeletedAt IS NULL;
```
Đây là:
```text id="soft04"
Soft Delete
```
Ưu điểm:
- Giữ history.
- Có khả năng restore.
- Dễ audit hơn.
- Tránh mất dữ liệu ngay lập tức.
Nhưng cũng có trade-off:
- Table lớn hơn.
- Query phức tạp hơn.
- Unique constraint cần thiết kế cẩn thận.
- Foreign key lifecycle phức tạp hơn.
---
# 30. DELETE vs Soft Delete vs TRUNCATE
Có thể hình dung:
```text id="soft05"
DELETE
↓
Hard delete selected rows
```
```text id="soft06"
Soft Delete
↓
Keep row
Mark as deleted
```
```text id="soft07"
TRUNCATE
↓
Clear entire table quickly
```
Ba cách giải quyết ba bài toán khác nhau.
---
# 31. Permission cũng khác nhau
`DELETE` là một DML operation.
User có quyền:
```text id="perm01"
DELETE
```
có thể chạy:
```sql id="perm02"
DELETE FROM Users;
```
Nhưng điều đó không đồng nghĩa họ được quyền:
```sql id="perm03"
TRUNCATE TABLE Users;
```
`TRUNCATE TABLE` yêu cầu permission cao hơn, thường liên quan tới:
```text id="perm04"
ALTER permission on the table
```
Đây cũng là một lý do để hạn chế `TRUNCATE` ở production.
---
# 32. TRUNCATE production table có risk gì?
Các risk chính gồm:
```text id="risk05"
No WHERE
No DELETE Trigger
Identity Reset
Foreign Key Restrictions
Large Data Loss
Audit Bypass
```
Sai một lệnh:
```sql id="risk06"
TRUNCATE TABLE Orders;
```
có thể xóa toàn bộ table.
Không có:
```sql id="risk07"
WHERE
```
để giới hạn phạm vi.
Do đó đây là command cần cực kỳ cẩn thận.
---
# 33. Best Practice trước khi TRUNCATE
Trước khi chạy:
```sql id="best01"
TRUNCATE TABLE SomeTable;
```
nên kiểm tra:
```text id="best02"
[ ] Đây có phải production không?
[ ] Có chắc cần xóa toàn bộ table không?
[ ] Table có DELETE trigger không?
[ ] Trigger có business/audit logic không?
[ ] Table có Foreign Key không?
[ ] Có cần giữ identity không?
[ ] Có backup hoặc recovery strategy không?
[ ] Có application nào đang sử dụng table không?
```
---
# 34. Câu hỏi phỏng vấn: DELETE và TRUNCATE khác gì nhau?
Một câu trả lời tốt:
> DELETE is a DML operation that can remove selected rows using a WHERE clause. It fires DELETE triggers and does not normally reset the identity seed.
>
> TRUNCATE TABLE removes all rows by deallocating data pages, does not support WHERE, does not fire DELETE triggers, and normally resets the identity seed.
>
> TRUNCATE is usually faster and uses less transaction log for large tables, but both DELETE and TRUNCATE can be rolled back inside an explicit transaction in SQL Server.
—
# 35. Câu hỏi phỏng vấn: TRUNCATE có chạy Trigger không?
Trả lời:
> No. TRUNCATE TABLE does not fire DELETE triggers because SQL Server does not process it as a row-by-row DELETE operation.
Có thể thêm:
> This is important if the application uses DELETE triggers for auditing or synchronization.
—
# 36. Câu hỏi phỏng vấn: TRUNCATE có rollback được không?
Trả lời:
> Yes, in SQL Server TRUNCATE can be rolled back if it is executed inside a transaction that has not been committed.
Ví dụ:
“`sql id=”inter01″
BEGIN TRAN;
TRUNCATE TABLE Users;
ROLLBACK;
“`
—
# 37. Câu hỏi phỏng vấn: TRUNCATE có ghi Transaction Log không?
Trả lời:
> Yes. TRUNCATE is logged, but it is minimally logged compared with DELETE because SQL Server mainly records page and extent deallocation rather than individual row deletions.
Không nên trả lời:
“`text id=”inter02″
TRUNCATE does not use transaction log
“`
vì không chính xác.
—
# 38. Câu hỏi phỏng vấn: DELETE 1 triệu row và TRUNCATE 1 triệu row khác gì?
Nếu:
“`sql id=”inter03″
DELETE FROM Logs;
“`
SQL Server phải xử lý row deletion và transaction logging nhiều hơn.
Nếu:
“`sql id=”inter04″
TRUNCATE TABLE Logs;
“`
SQL Server có thể deallocate pages/extents.
Vì vậy `TRUNCATE` thường:
“`text id=”inter05″
faster
+
less logging
+
less resource usage
“`
khi mục tiêu là clear toàn bộ table.
—
# 39. Cách nhớ nhanh
Có thể nhớ `DELETE` bằng:
“`text id=”remember01″
DELETE
↓
Rows
↓
WHERE
↓
Trigger
↓
Keep Identity
“`
Còn `TRUNCATE`:
“`text id=”remember02″
TRUNCATE
↓
Whole Table
↓
Deallocate Pages
↓
No DELETE Trigger
↓
Reset Identity
“`
—
# 40. Kết luận
`DELETE` và `TRUNCATE` không đơn giản chỉ là:
“`text id=”end01″
slow delete
vs
fast delete
“`
Bản chất khác nhau.
`DELETE` phù hợp khi cần:
“`text id=”end02″
Selective deletion
Business logic
Trigger
Audit
Cascade
“`
Trong khi `TRUNCATE` phù hợp hơn khi:
“`text id=”end03″
Clear entire table
No delete trigger requirement
No foreign key restriction
Need high performance
“`
Điểm quan trọng nhất cần nhớ:
> `DELETE` hoạt động với row deletion semantics và có thể kích hoạt DELETE trigger.
Trong khi:
> `TRUNCATE TABLE` chủ yếu deallocate các data pages, không chạy DELETE trigger và thường reset identity.
Với production database, trước khi chạy `TRUNCATE`, luôn kiểm tra:
“`text id=”end04″
Trigger
Foreign Key
Identity
Audit
Backup
Business dependency
“`
vì trong nhiều hệ thống, vấn đề không nằm ở việc dữ liệu bị xóa, mà nằm ở những business process đáng lẽ phải chạy khi dữ liệu bị xóa nhưng đã bị `TRUNCATE` bỏ qua.