大体积数据从varbinary(max)迁移至FILESTREAM的健壮SQL脚本问询

背景：数据库中文件存储在普通VARBINARY(MAX)列，计划改为FILESTREAM存储，需编写支持断点续传、处理中断/错误的迁移脚本，数据规模为数千文件、数百GB。以下是编写的SQL脚本：

-- Rename old column and create a new column with the old name.
-- Check if the temporary renamed column exists in case this is
-- resuming from a failed prior attempt.
IF NOT EXISTS (SELECT 1 
               FROM sys.columns 
               WHERE [name] = 'old_DataColumn' AND [object_id] = OBJECT_ID('dbo.TableWithData'))
BEGIN
    EXEC sp_rename 'dbo.TableWithData.DataColumn', 'old_DataColumn', 'COLUMN';

    ALTER TABLE [TableWithData]
    ADD [DataColumn] VARBINARY(MAX) FILESTREAM NULL;
END

DECLARE @Id UNIQUEIDENTIFIER;

DECLARE [DataTransferCursor] CURSOR LOCAL FOR 
    SELECT [Id] 
    FROM [TableWithData] 
    WHERE [DataColumn] IS NULL AND [old_DataColumn] IS NOT NULL;
OPEN [DataTransferCursor];

FETCH NEXT FROM [DataTransferCursor] INTO @Id;
WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE [TableWithData]
    SET [DataColumn] = [old_DataColumn]
    WHERE [Id] = @Id;

    FETCH NEXT FROM [DataTransferCursor] INTO @Id;
END

CLOSE [DataTransferCursor];
DEALLOCATE [DataTransferCursor];

-- Do not drop old column until all data has been copied.
IF NOT EXISTS(SELECT 1 
              FROM [TableWithData] 
              WHERE [DataColumn] IS NULL AND [old_DataColumn] IS NOT NULL)
BEGIN
    ALTER TABLE [TableWithData]
    DROP COLUMN [old_DataColumn];
END

咨询问题：

1. 游标查询与删除列前的检查是否足以避免提前删除列？
2. 是否有更高效的实现方式？
3. 针对本地游标，CLOSE和DEALLOCATE操作是否必要？

1. 游标查询与删除列前的检查是否足以避免提前删除列？ #

是的，这套逻辑足够可靠：

• 游标仅筛选DataColumn为NULL且old_DataColumn非NULL的行，确保只处理未完成迁移的记录；
• 删除列前的IF NOT EXISTS查询会校验所有存有旧数据的行都已完成迁移，只有当不存在未迁移记录时才会执行删除操作；
• 即使脚本中途中断，再次执行时游标会自动跳过已完成迁移的行，删除列的检查也会严格卡着全量迁移完成的条件，不会提前删掉旧列。

2. 是否有更高效的实现方式？ #

当前逐行更新的方式对数百GB规模的数据来说效率偏低，可从以下方向优化：

• 批量更新：每次处理一批（如100-1000条）记录，减少事务日志开销和循环次数，示例逻辑：

  DECLARE @BatchSize INT = 100;
  WHILE 1=1
  BEGIN
      UPDATE TOP(@BatchSize) [TableWithData]
      SET [DataColumn] = [old_DataColumn]
      WHERE [DataColumn] IS NULL AND [old_DataColumn] IS NOT NULL;
      
      IF @@ROWCOUNT = 0 BREAK;
  END
  

• 使用SSIS或批量工具：超大规模数据下，SSIS能更高效处理迁移，支持断点续传和更优的内存管理，避开SQL脚本单线程处理的瓶颈；
• 事务控制：批量更新时可为每批数据添加短事务，减少锁持有时间，降低对业务的影响。

3. 针对本地游标，CLOSE和DEALLOCATE操作是否必要？ #

• CLOSE：本地游标在批处理结束后会自动关闭，但显式执行是良好习惯，能及时释放游标占用的资源，避免不必要的资源持有；
• DEALLOCATE：本地游标在批处理结束后会自动释放，但显式执行可确保资源被立即回收，尤其在脚本多次执行或逻辑复杂的场景下，能避免潜在的资源泄漏；
• 总结：不是强制要求，但属于最佳实践，能让脚本更健壮。

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