Cú pháp xóa dữ liệu DELETE và TRUNCATE trong SQL Server

SQL Server cung cấp 2 phương pháp để xóa dữ liệu, DELETE và TRUNCATE. Cú pháp của hai lệnh này như sau:

DELETE

DELETE dbo.Tblxxx WHERE...

hoặc

DELETE a FROM dbo.Tblxxx a WHERE...

Khi cần xóa dữ liệu với điều kiện liên quan đến bảng khác:

DELETE a

FROM dbo.Tblxxx a

JOIN dbo.Tblyyy b ON a.Col1 = b.Col1

hoặc:

DELETE a

FROM dbo.Tblxxx a

WHERE EXISTS(SELECT 1 FROM dbo.Tblyyy b WHERE a.Col1 = b.Col1)

TRUNCATE không có tùy biến nào

TRUNCATE TABLE dbo.Tblxxx

Tuy cùng để xóa dữ liệu, nhưng hai lệnh này có những khác nhau cơ bản:

+ DELETE cung cấp các lựa chọn để xóa những dòng dữ liệu thỏa mãn các điều kiện nhất định, như WHERE hoặc JOIN với các bảng khác.
TRUNCATE không có lựa chọn nào, mà luôn cắt bỏ toàn bộ dữ liệu của bảng. Nói cách khác, ta không thể TRUNCATE 1 nửa hay 1 phần của bảng.

+ DELETE hỗ trợ transaction. Khi lệnh DELETE nằm trong 1 transaction và trong một tình huống nào đó transaction được ROLLBACK thì các bản ghi bị xóa bởi lệnh DELETE sẽ trở lại bảng không có gì suy xuyển.
TRUNCATE thì ngược lại, không hỗ trợ transaction. Một khi đã thực hiện thì không thể lấy lại dữ liệu được nữa.

+ DELETE khi thực hiện bao gồm quá trình tìm các bản ghi thỏa mãn điều kiện của câu lệnh, và xóa các bản ghi này. Việc tìm các bản ghi cần xóa được thực hiện giống hệt như một câu lệnh SELECT, cũng tối ưu hóa, lựa chọn giữa các phương án thực hiện khác nhau và chọn ra phương án tối ưu (dựa vào index, statistics…).
TRUNCATE thì chỉ có một phương án thực hiện duy nhất, đó là cắt bỏ tất cả các dòng dữ liệu của bảng.

+ Với DELETE, các bản ghi bị xóa sẽ được kiểm tra xem có vi phạm ràng buộc FOREIGN KEY không. Ví dụ ta có hai bảng MAT_HANG và BAN_HANG là quan hệ 1-n thông qua MA_MH; nếu MA_MH=1 đã có giao dịch, nghĩa là bảng BAN_HANG đã có bản ghi với MA_MH=1, thì khi DELETE bản ghi với MA_MH=1 từ bảng MAT_HANG (bảng cha) SQL SERVER sẽ báo lỗi và không cho xóa.
Nếu trước đó, khi ta định nghĩa ràng buộc FOREIGN KEY mà có lựa chọn CASCADE DELETE, thì thay vì báo lỗi SQL Server sẽ đồng thời xóa hết các bản ghi trong cả bảng BAN_HANG với MA_MH=1.

+ TRUNCATE thì không có những đoạn kiểm tra dài dòng như thế. Nếu bảng có ràng buộc FOREIGN KEY, SQL Server sẽ báo lỗi và không cho thực hiện (nhớ là lựa chọn CASCADE DELETE trong khai báo FOREIGN KEY chỉ ảnh hưởng đến lệnh DELETE chứ không tác dụng đối với TRUNCATE).

+ Vì DELETE hỗ trợ transaction và dùng transaction log, nó có thể dùng với bảng nằm trong một replication hoặc database có dùng log shipping.
TRUNCATE thì vì không ghi gì vào transaction log nên khi gặp một trong các tình huống trên sẽ bị từ chối ngay.

+ Với DELETE, nếu bảng có index thì các index cũng sẽ được cập nhật để xóa đi các node tương ứng với các bản ghi bị xóa.
TRUNCATE thì rất đơn giản, các index của bảng cũng bị cắt cụt theo.

+ DELETE không ảnh hưởng đến giá trị IDENTITY. Nếu bảng có 100 bản ghi và cột IDENTITY có giá trị từ 1-100; nay ta DELETE bản ghi có cột IDENTITY=100 rồi INSERT một bản ghi mới; bản ghi mới sẽ có cột IDENTITY=101.
TRUNCATE luôn đặt lại IDENTITY trở về 1. Bản ghi đầu tiên được INSERT sau khi TRUNCATE sẽ có cột IDENTITY=1.

+ DELETE thực ra chỉ đánh dấu xóa các bản ghi chứ ngay sau đó dữ liệu của các bản ghi bị xóa vẫn nằm nguyên tại chỗ. Dần dần khi ta INSERT thêm dữ liệu vào bảng thì các bản ghi mới sẽ ghi đè lên các vùng lưu trữ đó. Ta có thể kiểm tra để thấy kích thước bảng không thay đổi ngay cả sau khi chạy DELETE FROM TblName (xóa hết các bản ghi).
TRUNCATE thì xóa hết dữ liệu đồng thời giải phóng vùng lưu trữ giành cho bảng, trả lại cho SQL Server. Ta có thể so sánh DELETE như là xóa file, còn TRUNCATE thì như format lại ổ cứng.

Vì những lý do trên, DELETE luôn luôn chậm hơn TRUNCATE. Càng có nhiều bản ghi DELETE càng chậm, còn TRUNCATE thì không phụ thuộc vào lượng dữ liệu. DELETE có phạm vi ứng dụng rộng hơn; còn TRUNCATE thì chỉ dùng được mỗi một việc, nhưng nó lại làm rất nhanh. Vì vậy, hãy nhớ dùng TRUNCATE khi có thể được.