三者皆可移除資料,但行為、效能、觸發器觸發、空間釋放、可回滾性截然不同。本文從相同點、六大差異點、速度比較、使用建議全面剖析。
TRUNCATE、無 WHERE 的 DELETE、DROP 都會刪除表內所有資料。
| 項目 | DELETE | TRUNCATE | DROP |
|---|---|---|---|
| 語句類型 | DML | DDL | DDL |
| 觸發器 | 觸發 AFTER/INSTEAD OF TRIGGER | 不觸發 | 不觸發 |
| 交易/回滾 | 記入交易日誌,可 ROLLBACK | 立即生效,不記錄逐筆日誌,不可回滾 | 立即生效,不可回滾 |
| 空間釋放 | 不釋放 Extent,High Water Mark 不動 | 預設釋放至 MINEXTENTS,可用 REUSE STORAGE 保留 | 釋放所有空間(含索引、約束、觸發器) |
| High Water Mark | 保持原位 | 重置至初始位置 | 表結構消失 |
| 適用對象 | Table / View | 僅 Table | Table / View / Index 等 |
DROP > TRUNCATE > DELETE
DELETE:逐筆刪除、逐筆寫日誌、維護索引,最慢。TRUNCATE:釋放資料頁、僅記錄頁釋放日誌、重置 HWM,極快。DROP:直接移除物件定義與所有資料,最快。DELETE WHERE ...,確保 Rollback Segment 足夠大。DROP TABLE。TRUNCATE TABLE(最快)。DELETE(較慢但安全)。TRUNCATE TABLE ... REUSE STORAGE 後重新匯入資料。TRUNCATE 會重設 Identity 欄位種子;若要保留種子請用 DELETE。FOREIGN KEY 參考的表無法 TRUNCATE,必須用 DELETE。DELETE 為 DML,寫入 Rollback Segment;TRUNCATE/DROP 為 DDL,不寫入,不可回滾。DELETE 不降低 HWM,TRUNCATE 會重置 HWM。理解三者在日誌、鎖定、空間、觸發器、Identity、外鍵限制上的差異,才能在正確場合選擇正確指令,兼顧效能與資料安全。