比CONCAT或+更高效的varchar(max)长字符串拼接方法

你的测试结果暴露了循环拼接字符串的核心问题：每次迭代都要复制整个已生成的字符串，时间复杂度为O(n²)，数据量越大，性能衰减越严重。以下是几种远优于循环++/CONCAT的方案：

1. 集合式拼接（最优推荐，SQL Server 2017+） #

使用STRING_AGG函数一次性聚合所有需要拼接的片段，完全避免循环带来的复制开销。

示例代码 #

DECLARE @S1 VARCHAR(MAX) = 'TEST';
DECLARE @S2 VARCHAR(MAX) = ' OK';
DECLARE @RepeatCount INT = 100000;

-- 生成重复的片段集合，再一次性拼接
WITH Numbers AS (
    SELECT TOP (@RepeatCount) 1 AS Num
    FROM sys.all_columns ac1
    CROSS JOIN sys.all_columns ac2
)
SELECT @S = STRING_AGG(CONCAT(@S1, @S2), '')
FROM Numbers;

PRINT LEN(@S);
PRINT @S;

2. XML PATH拼接（兼容SQL Server 2016及更早版本） #

如果你的SQL Server版本不支持STRING_AGG，可以用FOR XML PATH实现高效的集合拼接。

示例代码 #

DECLARE @S1 VARCHAR(MAX) = 'TEST';
DECLARE @S2 VARCHAR(MAX) = ' OK';
DECLARE @RepeatCount INT = 100000;
DECLARE @S VARCHAR(MAX);

WITH Numbers AS (
    SELECT TOP (@RepeatCount) 1 AS Num
    FROM sys.all_columns ac1
    CROSS JOIN sys.all_columns ac2
)
SELECT @S = (
    SELECT CONCAT(@S1, @S2)
    FROM Numbers
    FOR XML PATH(''), TYPE
).value('.', 'VARCHAR(MAX)');

PRINT LEN(@S);
PRINT @S;

3. 预分配长度的循环优化（仅当必须用循环时） #

如果业务逻辑强制要求循环，预分配VARCHAR(MAX)的长度可以减少内存扩容的次数，小幅提升性能：

示例代码 #

DECLARE @S1 VARCHAR(MAX) = 'TEST';
DECLARE @S2 VARCHAR(MAX) = ' OK';
DECLARE @S VARCHAR(MAX);
DECLARE @lni INT = 0;
DECLARE @RepeatCount INT = 100000;
DECLARE @SingleLen INT = LEN(@S1) + LEN(@S2);

-- 预分配足够的长度
SET @S = SPACE(@RepeatCount * @SingleLen);
SET @S = ''; -- 清空但保留内存空间

WHILE (@lni < @RepeatCount) BEGIN
    SET @lni = @lni + 1;
    SET @S = @S + @S1 + @S2;
END

PRINT LEN(@S);
PRINT @S;

性能差异说明 #

• 循环++/CONCAT：每次迭代复制整个字符串，10万次循环的总复制量约为(1+2+...+100000)*8 = 40000400000字节（约37GB），开销极大。
• 集合式拼接：仅需遍历一次片段集合，一次性生成最终字符串，时间复杂度为O(n)，10万次拼接耗时通常在毫秒级，远优于循环方案。

内容的提问来源于stack exchange，提问作者Sven Alexander Gal