SQL DROP、DELETE、TRUNCATE 深度對比：異同、效能與適用場景全解析

刪除資料是資料庫操作的基礎，但 DROP、DELETE、TRUNCATE 三者在機制、效能、副作用上差異極大。誤用可能導致資料遺失、效能雪崩或無法回滾。本文從相同點、核心差異、效能排序、安全性、適用場景、內部機制六大維度徹底釐清。

一、相同點

• 皆可移除資料表中的資料列。
• TRUNCATE、無 WHERE 的 DELETE、DROP 均會清空表內所有資料。

二、核心差異總覽表

三、效能排序 (大資料量時)

DROP > TRUNCATE > DELETE

• DROP：僅更新系統目錄，極快。
• TRUNCATE：元資料操作 + 頁釋放記錄，秒級清空億級資料表。
• DELETE：逐列記錄、索引維護、觸發器觸發，大表清空可能耗時數小時並塞爆交易記錄檔。

四、安全性警示

五、適用場景決策樹

1. 刪除部分資料 → DELETE WHERE ... (確保 Rollback Segment 足夠大)。
2. 刪除整張表 (結構+資料) → DROP TABLE。
3. 保留結構、清空所有資料、無交易需求、無觸發器依賴 → TRUNCATE TABLE (最快)。
4. 保留結構、清空所有資料、需在交易中/觸發觸發器 → DELETE FROM table (較慢但安全)。
5. 整理表碎片、重置高水位、重置身分識別 → TRUNCATE TABLE ... REUSE STORAGE 後重新匯入資料。

六、內部機制深度補充

DELETE 逐列處理流程

1. 定位符合條件的列。
2. 對每列：加鎖 → 寫入 Undo/Redo Log → 標記刪除 → 釋放鎖。
3. 索引同步維護 (每個非叢集索引皆需刪除對應鍵值)。
4. 觸發 AFTER DELETE / INSTEAD OF DELETE。

TRUNCATE 頁級釋放流程

1. 取得 Schema Modification (Sch-M) 鎖。
2. 將所有資料頁標記為未配置，更新 IAM/PFS/GAM 頁面。
3. 在交易記錄中僅記錄「頁釋放」動態，不記錄個別列。
4. 重置身分識別計數器 (若有)。
5. 釋放 Sch-M 鎖。

DROP 物件移除流程

1. 取得 Sch-M 鎖。
2. 從系統目錄移除物件定義 (sys.objects, sys.indexes, sys.columns 等)。
3. 釋放所有配置頁面 (IAM 鏈)。
4. 使依賴預存程序/函數/視圖進入 INVALID 狀態 (需重新編譯)。

七、常見誤區澄清

八、總結建議

將 DELETE、TRUNCATE、DROP 視為三種不同「暴力程度」的工具：

• DELETE = 手術刀：精準、可控、留痕、可後悔。
• TRUNCATE = 砍刀：乾淨利落、不留痕、不可後悔、保留骨架。
• DROP = 炸藥：連骨架一起炸平、徹底消失。

根據業務需求、資料量、復原需求選擇正確工具，是 DBA 與開發者的基本功。