萬盛學電腦網

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

SQL Server 快速刪除重複資料:四板斧法與現代語法對比

傳統痛點

游標逐列比對刪除重複資料:百萬級資料執行數小時,開發人員噩夢。

四板斧法 (企業管理師圖形介面操作)

  1. 建立同結構臨時表:CREATE TABLE Products_temp (...同 Products 結構...);
  2. 加唯一索引並勾選「忽略重複值」:企業管理器 → 管理索引 → 新增 → 唯一值 + 忽略重複值。
  3. 複製資料:INSERT INTO Products_temp SELECT * FROM Products; → 系統自動忽略重複鍵,回傳「已忽略重複的鍵」。
  4. 切換資料:DELETE Products; INSERT INTO Products SELECT * FROM Products_temp; DROP TABLE Products_temp;

優點:幾乎不寫碼、速度極快 (大量複製 + 索引去重)、安全。
關鍵:唯一索引欄位決定何謂「重複」,務必確認正確。

現代 T-SQL 最佳實踐 (SQL 2005+)

方法一:ROW_NUMBER() 保留第一筆

WITH CTE AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY ProductName, Unit, UnitPrice ORDER BY ProductID) AS rn
    FROM Products
)
DELETE FROM CTE WHERE rn > 1;

方法二:分組最小主鍵刪除

DELETE p1
FROM Products p1
INNER JOIN (
    SELECT MIN(ProductID) AS MinID, ProductName, Unit, UnitPrice
    FROM Products
    GROUP BY ProductName, Unit, UnitPrice
    HAVING COUNT(*) > 1
) p2 ON p1.ProductName = p2.ProductName
       AND p1.Unit = p2.Unit
       AND p1.UnitPrice = p2.UnitPrice
       AND p1.ProductID > p2.MinID;

方法三:SWITCH PARTITION (大表、企業版)


-- 1. 建立同結構分區表 (單分區)
-- 2. SWITCH 進去 → 建立唯一索引 → SWITCH 回原表
-- 適合極大資料量、需最小鎖定時間

效能與適用性比較

方法適用版本速度鎖定影響備註
四板斧 (UI+BCP)所有極快Sch-M 鎖 (切換時)需圖形介面、索引欄位定重複鍵
ROW_NUMBER()2005+快表鎖/頁鎖標準語法、可讀性佳、支援複雜去重邏輯
分組 JOIN 刪除2000+中表鎖相容舊版、無視窗函數
SWITCH PARTITION2005+ Ent毫秒級極小 (Sch-M 瞬間)需企業版、分區表、預先規劃

防重複根治

  • 資料庫層:UNIQUE CONSTRAINT / UNIQUE INDEX (IGNORE_DUP_KEY = ON 可靜默忽略)。
  • 應用層:寫入前檢查、冪等性設計。
  • ETL 層:來源去重、暫存表去重再載入。
copyright © 萬盛學電腦網 all rights reserved