连接 nvarchar(max) 值似乎不起作用(+= 作为 = 工作) [英] Concatenating nvarchar(max) values doesn't seem to work (+= working as =)

问题描述

使用 SQL Server 2012，我发现尝试基于表中的 nvarchar(max) 列构建字符串似乎无法正常工作.它似乎覆盖，而不是追加.任意示例:

Using SQL Server 2012, I've found that trying to build up a string based on an nvarchar(max) column in a table doesn't seem to work correctly. It seems to overwrite, instead of append. Arbitrary Example:

    DECLARE @sql nvarchar(max);
    SELECT @sql = N'';
    SELECT @sql += [definition] + N'
GO
'
    FROM sys.sql_modules 
    WHERE OBJECT_NAME(object_id) LIKE 'dt%'
    ORDER BY OBJECT_NAME(object_id);

    PRINT @sql;

这应该打印出数据库中所有各种 dt_ 表的所有 SQL 模块定义，由 GO 分隔，作为可以运行的脚本.但是......这仅打印出最后一个模块定义，而不是所有模块的总和.它的行为就好像+="只是一个=".

This SHOULD print out all the SQL module definitions for all the various dt_ tables in the database, separated by GO, as a script that could then be run. However... this prints out only the LAST module definition, not the sum of all of them. It's behaving as if the "+=" were just an "=".

如果你稍微改变一下......例如将 [definition] 转换为 nvarchar(4000)，它会突然按预期工作.此外，如果您选择不是 nvarchar(max) 或 varchar(max) 类型的任何其他列，它会按预期工作.示例:

If you change it just slightly... cast [definition] to an nvarchar(4000) for example, it suddenly works as expected. Also, if you choose any other column that is NOT an nvarchar(max) or varchar(max) type, it works as expected. Example:

DECLARE @sql nvarchar(max);
SELECT @sql = N'';
SELECT @sql += CAST([definition] AS nvarchar(4000)) + '
GO
'
FROM sys.sql_modules 
WHERE OBJECT_NAME(object_id) LIKE 'dt%'
ORDER BY OBJECT_NAME(object_id);

PRINT @sql;

这是一个已知的错误吗?或者这是否按预期工作?难道我做错了什么?有什么办法可以让我正常工作吗?我尝试了十几种不同的方法，包括确保连接中的每个表达式都是相同的 nvarchar(max) 类型，包括字符串文字.

Is this a known bug? Or is this working as expected? Am I doing something wrong? Is there any way for me to make this work correctly? I've tried a dozen different things, including ensuring every expression in the concatenation is the same nvarchar(max) type, including the string literal.

注意:这个例子只是一个说明问题的例子，并不是我在现实生活中想要做的.如果您的数据库没有定义dt*"表，您可以更改 WHERE 子句以在您想要的任何数据库中指定任何一组表或存储过程，您将获得相同的结果......只有最后一个出现在@sql 字符串中，就好像你只是做了="而不是+=".此外，明确说明@sql = @sql +"的行为方式相同...适用于除 nvarchar(max) 或 varchar(max) 之外的所有字符串类型.

NOTE: The example is just an example that shows the problem, and not exactly what I'm trying to do in real life. If your database doesn't have the "dt*" tables defined, you can change the WHERE clause to specify any group of tables or stored procedures in any database you want, you'll get the same result... only the last one shows up in the @sql string, as if you just did "=" instead of "+=". Also, explicitly stating "@sql = @sql + " behaves the same way... works correctly with every string type EXCEPT nvarchar(max) or varchar(max).

我已经确认 [definition] 值都不是 NULL，因此没有发生 NULL 恶作剧.

I've verified that none of the [definition] values is NULL as well, so there are no NULL shenanigans going on.

推荐答案

补充@HABO 的评论，聚合字符串连接的行为是未定义的，结果取决于计划.使用 FOR XML 提供确定性结果并遵守 ORDER BY 子句:

To add on to @HABO's comment, the behavior of aggregate string concatenation is undefined and results are plan dependent. Use FOR XML to provide deterministic results and honor the ORDER BY clause:

DECLARE @sql nvarchar(max);

SET @sql = 
(SELECT [definition] + N'
GO
'
FROM sys.sql_modules 
WHERE OBJECT_NAME(object_id) LIKE 'dt%'
ORDER BY OBJECT_NAME(object_id)
FOR XML PATH(''), TYPE).value('(./text())[1]', 'nvarchar(MAX)');

PRINT @sql; --SSMS will truncate long strings

这篇关于连接 nvarchar(max) 值似乎不起作用(+= 作为 = 工作)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！