基于多列重复删除SQL Server表中重复记录的方法

我在SQL Server数据库中有一张5列表，所有列都存在重复数据，无单一列可作为主键。其中TheItem、TheDate、TheValue三列组合必须唯一（可作为复合主键），且这三列均无NULL值。

我已经用SQL识别出重复项，但不知道怎么修改代码来删除这些重复行，每组只保留一条记录（不关心删除哪条，TheComments均为NULL，也不关心保留哪个TheFile）。原表有超过300万条记录，示例数据如下：

识别重复项的SQL代码 #

SELECT 
    TheItem, TheDate, TheValue, Count(*) AS TheCount
FROM
    TheTable
GROUP BY 
    TheItem, TheDate, TheValue
HAVING 
    COUNT(*) > 1
ORDER BY 
    TheCount DESC

查询结果（约40万条记录） #

最优删除方案（针对百万级数据） #

方法1：使用ROW_NUMBER()窗口函数（推荐，性能稳定） #

利用窗口函数给每组重复数据编号，保留编号为1的行，删除其余行。逻辑清晰，对大数据量友好，可通过索引优化性能。

WITH CTE_Duplicates AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY TheItem, TheDate, TheValue ORDER BY (SELECT NULL)) AS RowNum
    FROM TheTable
)
DELETE FROM CTE_Duplicates
WHERE RowNum > 1;

说明：ORDER BY (SELECT NULL)表示不指定排序规则，SQL Server会随机保留一行，符合"不关心保留哪条"的需求。若需指定保留规则（比如保留最新的TheFile），可将(SELECT NULL)替换为TheFile DESC之类的排序条件。

方法2：临时表/新表替换（适合超大量重复数据） #

若重复数据占比过高（如超过30%），直接删除会产生大量日志，效率较低。可先将去重后的数据存入临时表，再替换原表：

-- 1. 创建临时表存储去重后的数据
SELECT DISTINCT *
INTO #Temp_TheTable
FROM TheTable;

-- 2. 清空原表（保留表结构）
TRUNCATE TABLE TheTable;

-- 3. 将去重后的数据插回原表
INSERT INTO TheTable
SELECT * FROM #Temp_TheTable;

-- 4. 清理临时表
DROP TABLE #Temp_TheTable;

说明：该方法优势是TRUNCATE比大量DELETE更快，日志量小。操作前务必备份原表，避免数据丢失。

方法3：添加复合唯一约束（彻底防止重复） #

删除重复数据后，建议给TheItem, TheDate, TheValue添加复合唯一约束，避免后续再出现重复数据：

ALTER TABLE TheTable
ADD CONSTRAINT UQ_TheTable_ItemDateValue UNIQUE (TheItem, TheDate, TheValue);

性能优化建议 #

• 操作前备份原表，避免意外数据丢失。
• 使用方法1时，先给TheItem, TheDate, TheValue创建复合索引，加速窗口函数的分组计算：

  CREATE NONCLUSTERED INDEX IX_TheTable_ItemDateValue ON TheTable (TheItem, TheDate, TheValue);
  

• 若数据库为完整恢复模式，执行DELETE操作前可开启批量日志恢复模式，减少日志生成，提升速度：

  ALTER DATABASE YourDatabaseName SET RECOVERY BULK_LOGGED;
  -- 执行删除操作后改回完整恢复模式
  ALTER DATABASE YourDatabaseName SET RECOVERY FULL;
  

内容的提问来源于stack exchange，提问作者Bonneroo