萬盛學電腦網

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

SQL TRUNCATE vs DELETE 實戰差異:觸發器、日誌與空間回收深度測試

測試環境建立

-- Table1:主表,含觸發器
CREATE TABLE Table1 (ID INT NOT NULL, FOID INT NOT NULL);
INSERT INTO Table1 VALUES (1,101),(2,102),(3,103),(4,104);

-- Table2:關聯表
CREATE TABLE Table2 (FOID INT NOT NULL);
INSERT INTO Table2 VALUES (101),(102),(103),(104);

-- 觸發器:刪除 Table1 時級聯刪除 Table2 對應 FOID
CREATE TRIGGER TG_Table1 ON Table1 AFTER DELETE AS
BEGIN
    DELETE FROM Table2 WHERE FOID IN (SELECT FOID FROM deleted);
END

測試 1:DELETE 觸發觸發器

DELETE FROM Table1 WHERE ID = 1;
-- 結果:Table1 剩 3 筆,Table2 同步刪除 FOID=101

測試 2:TRUNCATE 不觸發觸發器

TRUNCATE TABLE Table1;
-- 結果:Table1 歸零,Table2 仍保留 4 筆資料!

測試 3:交易日誌記錄差異

DELETE 日誌

CHECKPOINT;
DELETE FROM Table2 WHERE FOID = 102;
SELECT * FROM fn_dblog(NULL,NULL);

日誌中出現 LOP_DELETE_ROWS / LCX_HEAP,逐行記錄刪除操作。

TRUNCATE 日誌

CHECKPOINT;
TRUNCATE TABLE Table2;
SELECT * FROM fn_dblog(NULL,NULL);

日誌無任何逐行刪除記錄,僅記錄頁面釋放與結構變更。

核心差異總結表

特性DELETETRUNCATE
觸發器✅ 觸發 AFTER DELETE❌ 不觸發
日誌記錄逐行記錄(可回滾單行)僅記錄頁面去配置(極少量)
身份識別欄保留種子值重置種子值
WHERE 條件支援不支援(全表清空)
外鍵約束檢查並可級聯表被參考時禁止執行
執行速度大量資料較慢極快(元資料操作)
空間回收不立即釋放頁面立即釋放所有資料頁
權限需求DELETE 權限ALTER TABLE 權限

選擇指南

  • 需觸發器、條件刪除、保留身份識別種子 → DELETE
  • 全表清空、重置身份識別、追求極致速度、無外鍵參考 → TRUNCATE
  • 大量刪除但保留部分資料 → 考慮 DELETE TOP (N) 分批、或建立新表插入保留資料再重命名

結語

TRUNCATE 是 DDL 級操作,以最小日誌代價完成整表重置;DELETE 是 DML 逐行操作,支援完整事務語意。理解兩者在觸發器、日誌、鎖定、空間回收上的本質差異,才能在資料維護、ETL、歸檔等場景做出正確選擇。

copyright © 萬盛學電腦網 all rights reserved