如何在T-SQL中高效删除SQL Server表中30万条指定记录？

核心思路 #

先把Excel数据高效导入临时表，再分批次匹配删除主表数据，规避单条操作或全量操作的性能瓶颈。

1. 解决Excel数据导入临时表慢的问题 #

你之前对BULK INSERT的理解有误——它完全支持百万级数据批量导入，1000条的限制是部分场景下表值参数的默认设置，并非BULK INSERT的上限。如果无法直接使用BULK INSERT，可尝试以下两种方式：

• 方式一：Excel转CSV后用bcp工具导入
  将Excel另存为CSV格式（注意处理好分隔符、引号及特殊字符），然后用bcp命令行工具（客户端工具，权限要求更低）导入临时表：

  bcp YourDatabase.dbo.#TempTable in "C:\your_data.csv" -S YourServerName -U YourUsername -P YourPassword -c -t, -r\n
  

  参数说明：

  • -c：采用字符格式导入
  • -t,：指定逗号为字段分隔符
  • -r\n：指定换行符为行结束标识

• 方式二：表值参数（TVP）批量插入
  先在数据库创建自定义表类型：

  CREATE TYPE DeleteCriteriaType AS TABLE (
      Col1 VARCHAR(50),
      Col2 INT,
      Col3 DATETIME,
      -- 补充其他用于匹配的列
      PRIMARY KEY (Col1, Col2, Col3) -- 按唯一键创建主键，加速后续匹配
  );
  

  然后在应用端（如C#、Python）读取Excel数据，按批次（建议每批次1万条）将数据传入TVP，插入临时表：

  INSERT INTO #TempTable (Col1, Col2, Col3)
  SELECT Col1, Col2, Col3 FROM @DeleteCriteria;
  

  该方式比单条INSERT效率提升数倍。

2. 分批次删除主表数据 #

即便临时表数据导入完成，一次性删除30万条记录也会引发日志暴涨、锁表时间过长等问题，建议分批次执行：

DECLARE @BatchSize INT = 10000; -- 每批次删除1万条，可根据服务器性能调整

WHILE 1 = 1
BEGIN
    DELETE TOP (@BatchSize) MainTable
    FROM MainTable
    INNER JOIN #TempTable Temp
        ON MainTable.Col1 = Temp.Col1
        AND MainTable.Col2 = Temp.Col2
        AND MainTable.Col3 = Temp.Col3; -- 按唯一匹配列关联

    IF @@ROWCOUNT = 0
        BREAK;

    WAITFOR DELAY '00:00:01'; -- 可选：每批次后暂停1秒，降低服务器压力
END

3. 额外优化建议 #

• 给临时表的匹配列创建非聚集索引，大幅加速关联匹配速度：

  CREATE NONCLUSTERED INDEX IX_Temp_DeleteCriteria ON #TempTable (Col1, Col2, Col3);
  

• 若主表的匹配列无索引，可临时创建非聚集索引（删除完成后再删除），需注意索引创建的时间成本，适合数据量极大的场景。
• 尽量在业务低峰期执行删除操作，避免影响正常业务。

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