如何提升SQL Server 8中批量INSERT语句的执行速度？

嘿，针对SQL Server里大规模插入慢的问题，我有不少实战过的提速方案，咱们一个个捋清楚：

• 临时禁用索引、约束和触发器
  插入大量数据时，每一行都要更新索引、检查约束（主键、外键、唯一性约束）、触发触发器，这些操作会把插入速度拖得极慢。你可以先把非聚集索引、外键约束、触发器暂时禁用，等插入完成后再重新启用并重建索引。注意主键这类聚集索引没法直接禁用，如果要优化可以考虑先删除再重建（操作前务必确保数据不会出现重复或冲突）。
  示例命令：

  -- 禁用所有非聚集索引
  ALTER INDEX ALL ON ExistingTable DISABLE;
  -- 禁用所有外键约束
  ALTER TABLE ExistingTable NOCHECK CONSTRAINT ALL;
  -- 禁用所有触发器
  DISABLE TRIGGER ALL ON ExistingTable;
  
  -- 执行你的大规模INSERT操作
  -- ...你的插入语句...
  
  -- 重建索引
  ALTER INDEX ALL ON ExistingTable REBUILD;
  -- 启用约束并检查数据合法性
  ALTER TABLE ExistingTable CHECK CONSTRAINT ALL;
  -- 启用触发器
  ENABLE TRIGGER ALL ON ExistingTable;
  

• 改用批量插入语法，避免循环单条插入
  别用循环一行一行插（看你示例里有变量循环的意思），直接用INSERT ... SELECT批量取数插入，或者一次性插入多条数据的语法。如果是从外部文件导入，用BULK INSERT或者OPENROWSET(BULK...)，这些都是SQL Server专门优化过的批量操作，效率比单条插入高几个量级。
  比如一次性插入多条数据：

  INSERT INTO ExistingTable (Col1, Col2, Col3)
  VALUES 
  (Val1, Val2, Val3),
  (Val4, Val5, Val6),
  ... -- 最多支持一次性插入1000条，或者直接从其他表批量取数
  

  从CSV文件批量导入的示例：

  BULK INSERT ExistingTable
  FROM 'C:\data\your_batch_data.csv'
  WITH (
      FIELDTERMINATOR = ',',
      ROWTERMINATOR = '\n',
      BATCHSIZE = 10000 -- 分批次插入，避免内存过载
  );
  

• 拆分大事务为小批量事务
  单个超大事务会生成巨量日志，不仅占用大量磁盘空间，还会因为日志写入的IO瓶颈拖慢速度。可以把大插入拆成多个小批量事务，比如每1万行提交一次，这样既减少日志压力，也避免万一失败要回滚整个大事务。
  示例伪代码：

  DECLARE @BatchSize INT = 10000;
  DECLARE @CurrentStart INT = 1;
  DECLARE @TotalRows INT = 250000; -- 你要插入的总行数
  
  WHILE @CurrentStart <= @TotalRows
  BEGIN
      BEGIN TRANSACTION;
      
      INSERT INTO ExistingTable (Col1, Col2)
      SELECT SourceCol1, SourceCol2
      FROM YourSourceDataSource
      WHERE DataID BETWEEN @CurrentStart AND @CurrentStart + @BatchSize - 1;
      
      COMMIT TRANSACTION;
      SET @CurrentStart = @CurrentStart + @BatchSize;
  END;
  

• 临时切换数据库恢复模式
  如果你的数据库是完整恢复模式，大规模插入会生成海量日志，导致IO瓶颈。可以临时切换到简单恢复模式（日志会自动截断，不会积累大量日志文件），插入完成后再切回完整模式，记得切回后立即做一次完整备份，避免日志链断裂。
  命令示例：

  -- 切换到简单恢复模式
  ALTER DATABASE YourDatabaseName SET RECOVERY SIMPLE;
  
  -- 执行大规模INSERT操作
  -- ...你的插入语句...
  
  -- 切回完整恢复模式
  ALTER DATABASE YourDatabaseName SET RECOVERY FULL;
  -- 立即做一次完整备份，修复日志链
  BACKUP DATABASE YourDatabaseName TO DISK = 'C:\backups\your_db_post_insert.bak';
  

• 优化存储环境和表结构
  把数据库的数据文件放在高速SSD上，开启即时文件初始化（需要给SQL Server服务账号对应的权限），这样SQL Server扩展数据文件时不用清零，能节省不少时间。另外，如果表有大字段（比如VARCHAR(MAX)、TEXT），可以把这些字段单独放在一个独立的文件组，避免大字段的写入影响其他字段的插入速度。

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