SQL Article: Delete and Truncate

I. Cách sử dụng:

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.DM_BenhNhan WHERE…

hoặc

DELETE bn FROM dbo.DM_BenhNhan bn WHERE…

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

DELETE bn

FROM dbo.DM_BenhNhan bn

JOIN dbo.BenhAn ba ON bn.MaBenhNhan = ba.MaBenhNhan

hoặc:

DELETE bn

FROM dbo.DM_BenhNhan bn

WHERE EXISTS(SELECT 1 FROM dbo.BenhAn ba WHERE bn.MaBenhNhan = ba.MaBenhNhan)

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

TRUNCATE TABLE dbo.DM_BenhNhan


II. Điểm khác biệt:

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 DM_BenhNhan(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.

– DELETE cho phép áp dụng đối với bảng ở server khác được nối qua linked server.

TRUNCATE không cho phép điều này, bạn chỉ có thể TRUNCATE bảng nằm trên cùng server.

 

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 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.


IV. Các đặc điểm chính:
1. Delete:
– Cho phép có bộ lọc (filter)
– Cho phép làm việc với các cơ sở dữ liệu liên kết (linked database)
– Cho phép sử dụng giao tác (TRANSACTION)
– Có thể được hỗ trợ bởi Foreign Key (CASCADE DELETE)
2. Truncate:
– Tốc độ cực nhanh
– Tái lập (Reset) ID của bảng
– Không thể:
+ Chạy trên các cơ sở dữ liệu liên kết (linked database)
+ Hỗ trợ đối với Foreign Key (tuy nhiên có thể xóa các bảng con trước sau đó xóa bảng liên kết vẫn được)
+ Sử dụng giao tác (TRANSACTION)

III. Các trường hợp sử dụng:

1. Delete:

– Sử dụng để xóa những record (bản ghi) dư thừa không còn giá trị sử dụng

– Xóa theo điều kiện để loại bỏ những gì không cần thiết

2. Truncate:

– Xóa dữ liệu tạm (bảng tạm)

– Xóa dữ liệu sau khi làm thử (Test process)

– Xóa dữ liệu để thêm mới hoàn toàn (Full Add New – sử dụng trong các bảng danh mục, danh sách)


IV. Các lới khuyên:
– Sử dụng TRUNCATE khi có thể (trong các trường hợp nêu trên)
– Hạn chế sử dụng DELETE vì rất dễ quên điều kiện

 

Vậy thì TRUNCATE = DROP TABLE + RECREATE TABLE

DELETE có làm phân mảnh dữ liệu trong table không vậy tác giả ??? Vì sau khi DELETE size của Table không giảm.

nói chi li thì khác nhau vì DROP+CREATE TABLE là tạo một bảng mới (OBJECT_ID mới), nếu bạn có 1 user có 1 số quyền SELECT/UPDATE… trên bảng đó thì sau khi DROP+CREATE TABLE user đó sẽ mất hết quyền trên bảng mới, bạn phải cấp lại. TRUNCATE thì không sao. Nếu bạn có 1 view dựa trên bảng đó với lựa chọn WITH SCHEMABINDING thì bạn không thể DROP TABLE (bạn phải DROP VIEW trước).

DELETE chính là một nguyên nhân gây phân mảnh dữ liệu trong bảng, vì các trang chứa bản ghi bị xóa sẽ được dùng lại cho các bản ghi mới. Hiện tượng tương tự cũng xảy ra đối với index.

 

Khi bạn TRUNCATE bảng, trường IDENTITY sẽ được reset trở về giá trị ban đầu (khi tạo bảng). Tuy nhiên có những trường hợp bạn không thể dùng TRUNCATE (xem thêm bài DELETE và TRUNCATE). Ví dụ bạn muốn đặt lại giá trị của trường IDENTITY trong bảng dbo.TableName về 1, bạn có thể dùng lệnh này:

 

DBCC CHECKIDENT (‘dbo.TableName’, RESEED, 1)

Tagged: , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: