SQL Server全表行批量更新的最优方法及循环性能疑问

问题背景 #

我之前用以下SQL创建了一个包含10万行随机数据的表：

-- 创建带主键的表
CREATE TABLE fyi_random (
 id INT,
 rand_integer INT,
 rand_number numeric(18,9),
 rand_datetime DATETIME,
 rand_string VARCHAR(80)
);
-- 插入随机值行
DECLARE @row INT;
DECLARE @string VARCHAR(80), @length INT, @code INT;
SET @row = 0;
WHILE @row < 100000
BEGIN
 SET @row = @row + 1;
 -- 生成随机字符串
 SET @length = ROUND(80*RAND(),0);
 SET @string = '';
 WHILE @length > 0
 BEGIN
 SET @length = @length - 1;
 SET @code = ROUND(32*RAND(),0) - 6;
 IF @code BETWEEN 1 AND 26
 SET @string = @string + CHAR(ASCII('a')+@code-1);
 ELSE
 SET @string = @string + ' ';
 END
 -- 插入记录
 SET NOCOUNT ON;
 INSERT INTO fyi_random VALUES (@row, ROUND(2000000*RAND()-1000000,0), ROUND(2000000*RAND()-1000000,9), CONVERT(DATETIME, ROUND(60000*RAND() - 30000, 9) ), @string)
END
PRINT 'Rows inserted: '+CONVERT(VARCHAR(20),@row);
GO

现在我想更新表中每一行的rand_string列为新的随机字符串，尝试用WHILE循环逐行更新时性能随行数增加急剧下降：

SET NOCOUNT ON;
UPDATE random_data SET rand_string = @string WHERE id = @row;

后来试过游标语句，性能稍好，但还是想知道：为什么WHILE循环这么慢？有没有更高效的实现方式？

为什么WHILE逐行更新性能拉胯？ #

这本质是因为SQL Server是为集合操作设计的，逐行更新违背了它的优化方向，具体原因有两点：

• 单条更新的累积开销：每执行一次UPDATE，SQL Server都要处理事务日志写入、行锁的获取与释放、执行计划的调度等操作。10万次单独的UPDATE相当于10万次独立的微型事务，这些开销累加起来会让IO和CPU负载飙升，行数越多越明显。
• 索引定位的额外成本：虽然id是主键（有聚集索引），但每次按id=@row定位行，都需要进行一次索引查找，10万次查找的成本远高于一次集合操作的扫描。

最优解决方案：基于集合的批量更新 #

直接用一次性更新所有行的方式，结合T-SQL的随机函数特性，完全不需要循环或游标，性能会提升几个数量级。

方案1：递归CTE生成随机字符串 #

利用递归CTE生成1到80的数字序列，再为每行拼接随机字符：

WITH NumberSequence AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM NumberSequence WHERE n < 80
)
UPDATE fyi_random
SET rand_string = (
    SELECT STRING_AGG(
        CASE 
            WHEN ROUND(32*RAND(CHECKSUM(NEWID())), 0) - 6 BETWEEN 1 AND 26 
            THEN CHAR(ASCII('a') + (ROUND(32*RAND(CHECKSUM(NEWID())), 0) - 6) - 1)
            ELSE ' '
        END, ''
    ) 
    FROM NumberSequence
    WHERE n <= ROUND(80*RAND(CHECKSUM(NEWID())), 0)
)
OPTION (MAXRECURSION 0);

这里用CHECKSUM(NEWID())作为RAND()的种子是关键——默认RAND()在同一批查询中只会生成一次随机值，用NEWID()可以保证每行的随机字符串都完全独立。

方案2：利用系统表简化实现 #

如果不想写递归CTE，可以用系统表（比如sys.all_objects）来生成字符序列，通过FOR XML PATH('')拼接字符串：

UPDATE fyi_random
SET rand_string = (
    SELECT TOP (ROUND(80*RAND(CHECKSUM(NEWID())), 0))
        CASE 
            WHEN ROUND(32*RAND(CHECKSUM(NEWID())), 0) - 6 BETWEEN 1 AND 26
            THEN CHAR(ASCII('a') + (ROUND(32*RAND(CHECKSUM(NEWID())), 0) - 6) - 1)
            ELSE ' '
        END
    FROM sys.all_objects
    FOR XML PATH('')
);

这种方式更简洁，系统表的行数足够生成最长80位的字符串，不需要额外创建序列。

为什么游标性能比WHILE循环稍好？ #

游标本质还是逐行操作，但它比手动WHILE循环高效一点的原因是：

• 默认的**快速只进游标（FAST_FORWARD）**会优化遍历逻辑，不需要每次都通过主键索引定位行，而是顺序扫描表，减少了索引查找的次数。
• 游标可以批量处理一些事务和锁的逻辑，减少了重复的调度开销。
  但游标依然是逐行更新，性能远不如集合操作，只是比最朴素的WHILE循环强一些。

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