游標逐列比對刪除重複資料:百萬級資料執行數小時,開發人員噩夢。
CREATE TABLE Products_temp (...同 Products 結構...);INSERT INTO Products_temp SELECT * FROM Products; → 系統自動忽略重複鍵,回傳「已忽略重複的鍵」。DELETE Products; INSERT INTO Products SELECT * FROM Products_temp; DROP TABLE Products_temp;優點:幾乎不寫碼、速度極快 (大量複製 + 索引去重)、安全。
關鍵:唯一索引欄位決定何謂「重複」,務必確認正確。
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;
-- 1. 建立同結構分區表 (單分區)
-- 2. SWITCH 進去 → 建立唯一索引 → SWITCH 回原表
-- 適合極大資料量、需最小鎖定時間
| 方法 | 適用版本 | 速度 | 鎖定影響 | 備註 |
|---|---|---|---|---|
| 四板斧 (UI+BCP) | 所有 | 極快 | Sch-M 鎖 (切換時) | 需圖形介面、索引欄位定重複鍵 |
| ROW_NUMBER() | 2005+ | 快 | 表鎖/頁鎖 | 標準語法、可讀性佳、支援複雜去重邏輯 |
| 分組 JOIN 刪除 | 2000+ | 中 | 表鎖 | 相容舊版、無視窗函數 |
| SWITCH PARTITION | 2005+ Ent | 毫秒級 | 極小 (Sch-M 瞬間) | 需企業版、分區表、預先規劃 |
UNIQUE CONSTRAINT / UNIQUE INDEX (IGNORE_DUP_KEY = ON 可靜默忽略)。