Bản chất cơ chế hoạt động

• DELETE: Là một câu lệnh DML (Data Manipulation Language). Nó xóa dữ liệu từng dòng một và ghi lại từng thao tác vào Transaction Log. Cho phép sử dụng mệnh đề WHERE để xóa có điều kiện, kích hoạt các Trigger liên quan và có thể Rollback nếu nằm trong một Transaction.
• TRUNCATE: Là một câu lệnh DDL (Data Definition Language). Nó hoạt động bằng cách giải phóng các trang dữ liệu (data pages) được cấp phát cho bảng thay vì xóa từng dòng. Không ghi chi tiết từng dòng vào Transaction Log, không kích hoạt Trigger, không thể dùng mệnh đề WHERE và tự động reset lại giá trị Identity (Auto-increment) về ban đầu.

So sánh hiệu năng và an toàn

• Hiệu năng: TRUNCATE nhanh hơn rất nhiều so với DELETE, đặc biệt trên các bảng có hàng triệu bản ghi vì nó ít tốn tài nguyên Log và khóa (locks) hơn.
• An toàn: DELETE an toàn hơn khi cần xóa có chọn lọc và có thể khôi phục dữ liệu nếu có lỗi xảy ra trong Transaction. TRUNCATE cực kỳ nguy hiểm nếu thực hiện nhầm vì dữ liệu bị xóa vĩnh viễn và không thể Rollback trong hầu hết các cấu hình thông thường.

Ví dụ minh họa

-- Xóa toàn bộ dữ liệu bảng logs nhưng giữ lại cấu trúc và reset identity
TRUNCATE TABLE system_logs;

-- Xóa dữ liệu có điều kiện trong bảng orders
DELETE FROM orders WHERE status = 'CANCELLED';

Lưu ý kinh nghiệm thực chiến

Trong môi trường Production, không bao giờ chạy TRUNCATE thủ công mà không có sự đồng ý của quản trị viên hệ thống. Hãy sử dụng DELETE kết hợp với Batching (chia nhỏ số lượng bản ghi xóa mỗi lần) để tránh gây ra hiện tượng Lock Escalation làm treo hệ thống.