萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL 中 DROP、DELETE 與 TRUNCATE 差異深度解析

SQL 中 DROP、DELETE 與 TRUNCATE 差異深度解析

三者皆可移除資料,但行為、效能、觸發器觸發、空間釋放、可回滾性截然不同。本文從相同點、六大差異點、速度比較、使用建議全面剖析。

相同點

TRUNCATE、無 WHERE 的 DELETE、DROP 都會刪除表內所有資料。

六大核心差異

項目DELETETRUNCATEDROP
語句類型DMLDDLDDL
觸發器觸發 AFTER/INSTEAD OF TRIGGER不觸發不觸發
交易/回滾記入交易日誌,可 ROLLBACK立即生效,不記錄逐筆日誌,不可回滾立即生效,不可回滾
空間釋放不釋放 Extent,High Water Mark 不動預設釋放至 MINEXTENTS,可用 REUSE STORAGE 保留釋放所有空間(含索引、約束、觸發器)
High Water Mark保持原位重置至初始位置表結構消失
適用對象Table / View僅 TableTable / View / Index 等

速度比較

DROP > TRUNCATE > DELETE

  • DELETE:逐筆刪除、逐筆寫日誌、維護索引,最慢。
  • TRUNCATE:釋放資料頁、僅記錄頁釋放日誌、重置 HWM,極快。
  • DROP:直接移除物件定義與所有資料,最快。

安全性與使用建議

⚠️ 謹慎使用 DROP / TRUNCATE:無備份時操作不可逆,資料遺失風險極高。
  1. 刪除部分資料:DELETE WHERE ...,確保 Rollback Segment 足夠大。
  2. 刪除整張表:DROP TABLE。
  3. 保留結構、清空所有資料:
    • 無交易需求、無觸發器需求 → TRUNCATE TABLE(最快)。
    • 需在交易中、需觸發 Trigger → DELETE(較慢但安全)。
  4. 整理表碎片:TRUNCATE TABLE ... REUSE STORAGE 後重新匯入資料。

補充細節

  • TRUNCATE 會重設 Identity 欄位種子;若要保留種子請用 DELETE。
  • 被 FOREIGN KEY 參考的表無法 TRUNCATE,必須用 DELETE。
  • DELETE 為 DML,寫入 Rollback Segment;TRUNCATE/DROP 為 DDL,不寫入,不可回滾。
  • Oracle 中 DELETE 不降低 HWM,TRUNCATE 會重置 HWM。

小結

理解三者在日誌、鎖定、空間、觸發器、Identity、外鍵限制上的差異,才能在正確場合選擇正確指令,兼顧效能與資料安全。

copyright © 萬盛學電腦網 all rights reserved