SQL資料庫中truncate、drop、delete差別

本文導讀：删除表中的資料的方法有delete,truncate, 其中TRUNCATE TABLE用于删除表中的所有行，而不記錄單個行删除操作。TRUNCATE TABLE 與沒有 WHERE 子句的 DELETE 語句類似；但是，TRUNCATE TABLE 速度更快，使用的系統資源和事務日志資源更少。下面介紹SQL中Truncate的用法

當你不再需要該表時， 用 drop；當你仍要保留該表，但要删除所有記錄時， 用 truncate；當你要删除部分記錄時（always with a WHERE clause), 用 delete.

Truncate是一個能夠快速清空資料表内所有資料的SQL文法。并且能針對具有自動遞增值的字段，做計數重置歸零重新計算的作用。

一、Truncate文法

[ { database_name.[ schema_name ]. | schema_name . } ]

table_name

[ ; ]

參數

database_name

資料庫的名稱。

schema_name

表所屬架構的名稱。

table_name

要截斷的表的名稱，或要删除其全部行的表的名稱。

二、Truncate使用注意事項

1、TRUNCATE TABLE 在功能上與不帶 WHERE 子句的 DELETE 語句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系統和事務日志資源少。

2、DELETE 語句每次删除一行，并在事務日志中為所删除的每行記錄一項。TRUNCATE TABLE 通過釋放存儲表資料所用的資料頁來删除資料，并且隻在事務日志中記錄頁的釋放。

3、TRUNCATE TABLE 删除表中的所有行，但表結構及其列、限制、索引等保持不變。新行辨別所用的計數值重置為該列的種子。如果想保留辨別計數值，請改用 DELETE。如果要删除表定義及其資料，請使用 DROP TABLE 語句。

4、對于由 FOREIGN KEY 限制引用的表，不能使用 TRUNCATE TABLE，而應使用不帶 WHERE 子句的 DELETE 語句。由于 TRUNCATE TABLE 不記錄在日志中，是以它不能激活觸發器。

5、TRUNCATE TABLE 不能用于參與了索引視圖的表。

6、對用TRUNCATE TABLE删除資料的表上增加資料時，要使用UPDATE STATISTICS來維護索引資訊。

7、如果有ROLLBACK語句，DELETE操作将被撤銷，但TRUNCATE不會撤銷。

三、不能對以下表使用 TRUNCATE TABLE

1、由 FOREIGN KEY 限制引用的表。（您可以截斷具有引用自身的外鍵的表。）

2、參與索引視圖的表。

3、通過使用事務複制或合并複制釋出的表。

4、對于具有以上一個或多個特征的表，請使用 DELETE 語句。

5、TRUNCATE TABLE 不能激活觸發器，因為該操作不記錄各個行删除。

四、TRUNCATE、Drop、Delete差別

1.drop和delete隻是删除表的資料(定義),drop語句将删除表的結構、被依賴的限制(constrain)、觸發器 (trigger)、索引(index);依賴于該表的存儲過程/函數将保留,但是變為invalid狀态。

2.delete語句是DML語言,這個操作會放在rollback segement中,事物送出後才生效;如果有相應的觸發器(trigger),執行的時候将被觸發。truncate、drop是DDL語言,操作後即 生效,原資料不會放到rollback中,不能復原,操作不會觸發trigger。

3.delete語句不影響表所占用的extent、高水線(high watermark)保持原位置不動。drop語句将表所占用的空間全部釋放。truncate語句預設情況下将空間釋放到minextents的 extent,除非使用reuse storage。truncate會将高水線複位(回到最初)。

4.效率方面:drop > truncate > delete

5.安全性:小心使用drop與truncate,尤其是在 沒有備份的時候,想删除部分資料可使用delete需要帶上where子句,復原段要足夠大,想删除表可以用drop,想保留表隻是想删除表的所有資料、 如果跟事物無關可以使用truncate,如果和事物有關、又或者想觸發 trigger,還是用delete,如果是整理表内部的碎片，可以用truncate跟上reuse stroage，再重新導入、插入資料。

6.delete是DML語句,不會自動送出。drop/truncate都是DDL語句,執行後會自動送出。

7、drop一般用于删除整體性資料 如表，模式，索引，視圖，完整性限制等；delete用于删除局部性資料 如表中的某一進制組

8、DROP把表結構都删了；DELETE隻是把資料清掉

9、當你不再需要該表時， 用 drop；當你仍要保留該表，但要删除所有記錄時， 用 truncate；當你要删除部分記錄時（always with a WHERE clause), 用 delete.