[SQL] DELETE 和 TRUNCATE

TRUNCATE 等於沒有 WHERE 的 DELETE，但是 TRUNCATE 的效率比較快，主要是因為資料刪除時，DELETE 會鎖定每一筆資料進行刪除並寫進交易紀錄檔，TRUNCATE 鎖定資料表和資料頁來取消配置（Deallocate）儲存資料的資料頁（Data Page），交易記錄檔內只會記錄資料頁的取消配置，因此相較之下，DELETE 會耗費一些時間。

之前學習 TRUNCATE 時，一直以為 TRUNCATE 不會產生交易紀錄（Log）且不可回復（Rollback），其實 MSDN 上寫得很清楚，TRUNCATE 是會產生交易紀錄只是相較於 DELETE ， TRUNCATE 產生的交易紀錄較少，既然有交易紀錄，當然就可以回復囉。

TRUNCATE 限制：

1. 無法使用 在 Foreign Key 連結的 Table：父子關係 Table 且設有Foreign Key，父 Table 無法使用TRUNCATE ，子 Table 可以。
2. TRUNCATE 無法觸發 Trigger。
3. 資料表內欄位有設定識別規格，TRUNCATE 會重設至初始值。

• 關聯刪除

DECLARE @Data1 Table (NO char(5))
DECLARE @Data2 Table (NO char(5))
 
INSERT INTO @Data1 VALUES ('1')
INSERT INTO @Data1 VALUES ('2')
INSERT INTO @Data1 VALUES ('3')
INSERT INTO @Data1 VALUES ('4')
INSERT INTO @Data1 VALUES ('5')
INSERT INTO @Data2 VALUES ('1')
INSERT INTO @Data2 VALUES ('3')
INSERT INTO @Data2 VALUES ('5')

-- 方法 1 利用 JOIN
DELETE T1
FROM @Data1 AS T1 JOIN @Data2 AS T2 ON T1.NO = T2.NO
  
-- 方法 2 利用 IN
DELETE FROM @Data1
WHERE NO IN (SELECT NO FROM @Data2)

• 分批刪除大量資料

大量刪除資料的過程中也可能導致使用者存取效能降低，因此透過分批的方式，降低對SQL Server的影響。

SET NOCOUNT ON

WHILE  1 = 1 -- 一直進入迴圈
  BEGIN
    DELETE TOP (1000) -- 利用 TOP 限制刪除筆數（1000為使用者自訂筆數、括號一定要存在）
    FROM TableName
    WHERE Condition -- 篩選條件

    IF @@ROWCOUNT = 0 -- 假如沒有刪除任何資料，則跳出迴圈
      BREAK
  END

使用上述語法必須注意 @@ROWCOUNT 只會傳回前一個 T-SQL 影響的資料數，假如該 Table 上有設定 Delete Trigger 或 Foreign Key 的 Delete Cascade 的話，@@ROWCOUNT 的回傳值，並不是真正該 Table 的刪除資料數。

• 參考資料：
• MSDN：TRUNCATE、DELETE、使用 TOP 來限制刪除的資料列 和 @@ROWCOUNT
• When are pages from a truncated table reused?
• TRUNCATE TABLE is non-logged
• Truncate Cannot Be Rolled Back Because It Is Not Logged 
• [SQL SERVER][Memo]誰說 truncate 操作不會寫到交易記錄檔
• Super SQL Server 分享 - 原來 Truncate 還可以回收空間
• 批次刪除大量資料時應搭配合適索引來降低Blocking
  
• [SQL]使用@@ROWCOUNT全域變數要注意的地方!
• 提高Delete效能