T-SQL聚合拼接或求和，不可靠（SELECT @x = @x + ...）

数据库开发, SQL Server
目录

背景

写T-SQL的人经常会看到通过如下方式进行字符串拼接或求和：

DECLARE @x_str VARCHAR(100) = '', @x_sum INT = 0;

-- 字符串拼接
SELECT @x_str = @x_str + col_str FROM Table_x;
PRINT @x_str;

-- 数值求和
SELECT @x_sum = @x_sum + col_int FROM Table_x;
PRINT @x_sum;

大多数情况下结果都是符合预期的，同时还会感叹这样写真的好简洁，T-SQL真的灵活好用呀。
本人接触MSSQL比较多，在项目中遇到字符串拼接的需求时，也偶尔这样写，直到前一阵子遇到不符合预期的拼接结果。

我的需求是将一个表的字段名用逗号拼接起来作为动态SQL的一部分，字段按表中的顺序排序。

写法一 (不符合预期)

DECLARE @cols VARCHAR(4000) = '', @crlf CHAR(2) = CHAR(13) + CHAR(10);

SELECT @cols =  @cols + @crlf + ',' + FORMATMESSAGE('%s - %s',LTRIM(ORDINAL_POSITION),COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE CONCAT(TABLE_SCHEMA,'.',TABLE_NAME) = 'Person.Address'
ORDER BY ORDINAL_POSITION

PRINT @cols
GO

结果如下：

,9 - ModifiedDate

写法二(符合预期)

DECLARE @cols VARCHAR(4000) = '', @crlf CHAR(2) = CHAR(13) + CHAR(10);

SELECT @cols =  @cols + @crlf + ',' + CONCAT(ORDINAL_POSITION,' - ',COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE CONCAT(TABLE_SCHEMA,'.',TABLE_NAME) = 'Person.Address'
ORDER BY ORDINAL_POSITION

PRINT @cols
GO

结果如下：

,1 - AddressID
,2 - AddressLine1
,3 - AddressLine2
,4 - City
,5 - StateProvinceID
,6 - PostalCode
,7 - SpatialLocation
,8 - rowguid
,9 - ModifiedDate

和上面写法唯一的区分在于SELECT部分所用的函数不一样。

原因

为何会出现如此不一致的结果呢？网上找了一些资料，原来以上这种写法的结果非常依赖于具体的物理数据存储和执行计划，返回的结果并不是确定性的。不推荐在生产环境上使用。下面来比较一下上面两种写法的执行计划： ( SET STATISTICS PROFILE ON 后查询 )

可以看到预期结果的执行计划是先Sort再Compute Scalar，而非预期的则是先Compute Scalar再Sort。
执行计划的改变引起了结果的改变。

推荐写法

SQL Server 2017版本之前推荐用XML的写法：

SELECT
(
    SELECT CHAR(13) + CHAR(10) + ',' + FORMATMESSAGE('%s - %s',LTRIM(ORDINAL_POSITION),COLUMN_NAME)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE CONCAT(TABLE_SCHEMA,'.',TABLE_NAME) = 'Person.Address'
    FOR XML PATH(''),TYPE
).value('./text()[1]','varchar(max)')

SQL Server 2017开始支持STRING_AGG函数，更方便：

SELECT STRING_AGG(FORMATMESSAGE('%s - %s',LTRIM(ORDINAL_POSITION),COLUMN_NAME),CHAR(13) + CHAR(10) + ',')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE CONCAT(TABLE_SCHEMA,'.',TABLE_NAME) = 'Person.Address'

相关说明

  • 和 SELECT @x = @x + ... 类似的不推荐写法还有用update variable的方法实现running total
  • XML写法中的,TYPE是为了生成XML-safe的格式，否则最终结果中会有些XML特殊字符比如大于号(<)会被转义，但加上TYPE之后结果类型是XML，无法隐式转换为字符串类型，所以再加上.value('./text()[1]','varchar(max)')转为字符串，转换之后不会有XML格式中的转义字符

相关参考