SQL Server 2008 R2 刪除重複資料三種方法效能比較

場景說明

表 tblMulCharge 以 orderno 為去重鍵，需保留首筆記錄刪除其餘重複列。資料量大時，方法選擇直接影響執行時間與鎖定爭用。

方法一：子查詢 + ROW_NUMBER() 過濾 (僅查詢)

SELECT * FROM (
    SELECT ROW_NUMBER() OVER (PARTITION BY orderno ORDER BY (SELECT 0)) AS RowNO, *
    FROM tblMulCharge
) t
WHERE t.RowNO > 1;

缺點：僅能查詢重複列，無法直接刪除；需搭配 JOIN 或 IN 子句才能刪除，效能較差。

方法二：CTE + ROW_NUMBER() 直接刪除 (中小資料量適用)

WITH ct01 AS (
    SELECT ROW_NUMBER() OVER (PARTITION BY orderno ORDER BY (SELECT 0)) AS rn
    FROM tblMulCharge
)
DELETE FROM ct01 WHERE rn > 1;

優點：語法簡潔、單陳述式完成。
缺點：大資料量時交易記錄膨脹、鎖定升級風險高，可能導致記錄檔暴增。

方法三：CTE 篩選後 INSERT INTO 新表再重命名 (大數據量推薦) ⭐

WITH ct01 AS (
    SELECT 
        [ID], [OriOrderNo], [OrderNo], [TotalAmount], [PayAmount],
        [ProviderAmount], [transaction_id], [PNRCode], [Consumer],
        [Provider], [SellerAccount], [BuyerAccount], [State], [PayTime],
        [PayInfo], [RefundTime], [refund_id], [refund_info],
        [RefundTimeV], [refund_id_V], [refund_info_V], [RefundAmount],
        ROW_NUMBER() OVER (PARTITION BY orderno ORDER BY (SELECT 0)) AS rn
    FROM tblMulCharge
)
SELECT 
    [ID], [OriOrderNo], [OrderNo], [TotalAmount], [PayAmount],
    [ProviderAmount], [transaction_id], [PNRCode], [Consumer],
    [Provider], [SellerAccount], [BuyerAccount], [State], [PayTime],
    [PayInfo], [RefundTime], [refund_id], [refund_info],
    [RefundTimeV], [refund_id_V], [refund_info_V], [RefundAmount]
INTO dbo.tblMulCharge_tmp
FROM ct01
WHERE rn = 1;

DROP TABLE dbo.tblMulCharge;
EXEC sp_rename 'dbo.tblMulCharge_tmp', 'tblMulCharge';

方法三優勢分析

生產環境注意事項

• 執行前務必完整備份
• 確認無外鍵參考、觸發器、複製發佈等相依物件
• 重建後需重新建立索引、統計資料、權限、擴充屬性
• 大表建議維護窗口執行，並監控磁碟空間