SQL Server TRUNCATE vs DELETE 深度解析：觸發器、日誌、效能實測

核心差異速查表

實測環境建立

-- 父表
CREATE TABLE Table1 (ID INT NOT NULL, FOID INT NOT NULL);
INSERT INTO Table1 VALUES (1,101),(2,102),(3,103),(4,104);

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

-- 觸發器：刪除父表同步刪除子表
CREATE TRIGGER TG_Table1 ON Table1 AFTER DELETE
AS
DELETE TA FROM Table2 TA JOIN deleted TB ON TA.FOID = TB.FOID;

測試 1：DELETE 啟動觸發器

DELETE FROM Table1 WHERE ID = 1;
SELECT * FROM Table1;  -- 剩 2,3,4
SELECT * FROM Table2;  -- 101 被級聯刪除

測試 2：TRUNCATE 不啟動觸發器

TRUNCATE TABLE Table1;
SELECT * FROM Table1;  -- 空
SELECT * FROM Table2;  -- 101,102,103,104 全保留！

日誌內容對比

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

CHECKPOINT;
TRUNCATE TABLE Table2;
SELECT * FROM fn_dblog(NULL,NULL) WHERE Operation = 'LOP_DELETE_ROWS';

觀察：TRUNCATE 無 LOP_DELETE_ROWS 記錄，僅有頁釋放操作（LOP_DEALLOC_EXTENT 等），故不觸發觸發器。

決策指南

• 需觸發器維護關聯資料、稽核軌跡 → DELETE。
• 清空整表、重置身分識別、無外鍵被參考、追求極致速度 → TRUNCATE。
• 大量刪除但需保留部分資料 → 考慮 DELETE 分批 + 索引，或 SWITCH PARTITION（Enterprise 版）。