SQL Server如何让Update每行独立计算Base64编码的随机值？

需求：更新现有表，为每行的指定列填充不同的32字节Base64编码随机值。

背景现象 #

无Base64编码时正常工作 #

直接使用CRYPT_GEN_RANDOM(32)进行更新，每行都会生成独立的随机值：

DECLARE @table TABLE (
    id int,
    bin varbinary(max) null
)

-- 插入测试数据
insert into @table (id) values (1)
insert into @table (id) values (2)
insert into @table (id) values (3)

-- 执行更新
update @table
set bin =  CRYPT_GEN_RANDOM(32)

-- 查看结果
select *
from @table

添加Base64编码后出现重复值 #

无论是直接使用子查询进行Base64编码，还是封装为标量UDF调用，更新后所有行的Base64值都相同：

直接子查询方式

DECLARE @table TABLE (
    id int,
    txt nvarchar(max) null
)

-- 插入测试数据
insert into @table (id) values (1)
insert into @table (id) values (2)
insert into @table (id) values (3)

-- 执行更新（所有行生成相同值）
update @table
set txt =  (SELECT CRYPT_GEN_RANDOM(32) FOR XML PATH(''), BINARY BASE64)

-- 查看结果
select *
from @table

标量UDF方式

先创建UDF：

CREATE FUNCTION ConvertBytesToBase64
(
    @bytes varbinary(max)
)
RETURNS nvarchar(max)
AS
BEGIN
    DECLARE @result nvarchar(max)
    SET @result = (SELECT @bytes FOR XML PATH(''), BINARY BASE64)
    RETURN @result
END
GO

调用UDF更新：

update @table
set txt = ConvertBytesToBase64(CRYPT_GEN_RANDOM(32))

问题原因 #

• CRYPT_GEN_RANDOM(32)作为直接的列赋值表达式时，SQL Server会逐行执行该函数，因此每行得到不同的随机值。
• 当将其包裹在标量子查询（(SELECT ... FOR XML PATH(''))）或标量UDF中时，SQL Server的查询优化器会将其视为常量表达式，仅计算一次，然后将结果复用至所有行，导致所有行得到相同的Base64值。
• 标量UDF在默认执行模式下（非内联），也容易被优化为单次计算，而非逐行触发。

解决方案 #

方法1：使用CROSS APPLY实现逐行计算 #

通过CROSS APPLY将随机值生成与Base64转换绑定到每一行，确保每行独立计算：

DECLARE @table TABLE (
    id int,
    txt nvarchar(max) null
)

insert into @table (id) values (1), (2), (3)

UPDATE t
SET txt = x.Base64Value
FROM @table t
CROSS APPLY (
    SELECT CRYPT_GEN_RANDOM(32) FOR XML PATH(''), BINARY BASE64
) x(Base64Value)

select * from @table

方法2：改用内联表值函数（ITVF） #

内联表值函数会被SQL Server展开到主查询中，实现逐行执行：

CREATE FUNCTION dbo.ConvertBytesToBase64_ITVF(@bytes varbinary(max))
RETURNS TABLE
AS
RETURN
SELECT (SELECT @bytes FOR XML PATH(''), BINARY BASE64) AS Base64Value
GO

调用方式：

UPDATE t
SET txt = x.Base64Value
FROM @table t
CROSS APPLY dbo.ConvertBytesToBase64_ITVF(CRYPT_GEN_RANDOM(32)) x

SQL Server 2019+的更优Base64编码方式 #

SQL Server 2022及以后：使用STRING_BINARY_BASE64函数 #

SQL Server 2022新增了STRING_BINARY_BASE64函数，直接将varbinary类型转换为Base64字符串，无需依赖XML操作，性能更优且语法简洁，同时天然支持逐行计算：

UPDATE @table
SET txt = STRING_BINARY_BASE64(CRYPT_GEN_RANDOM(32))

SQL Server 2019：优化内联实现 #

2019版本没有专门的Base64转换函数，推荐使用CROSS APPLY结合XML的方式（同解决方案1），或使用内联表值函数，避免标量UDF的性能和执行次数问题。

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