sql-server - nvarchar 连接/索引/nvarchar(max) 令人费解的行为

Question

我今天在 SQL Server(2008R2 和 2012)中遇到了一个非常奇怪的问题。我正在尝试结合使用连接和 select 语句来构建一个字符串。

我已经找到了解决方法，但我真的很想了解这里发生了什么以及为什么它没有给我预期的结果。有人可以给我解释一下吗？

http://sqlfiddle.com/#!6/7438a/1

根据要求，这里还有代码:

-- base table
create table bla (
    [id] int identity(1,1) primary key,
    [priority] int,
    [msg] nvarchar(max),
    [autofix] bit
)

-- table without primary key on id column
create table bla2 (
    [id] int identity(1,1),
    [priority] int,
    [msg] nvarchar(max),
    [autofix] bit
)

-- table with nvarchar(1000) instead of max
create table bla3 (
    [id] int identity(1,1) primary key,
    [priority] int,
    [msg] nvarchar(1000),
    [autofix] bit
)

-- fill the three tables with the same values
insert into bla ([priority], [msg], [autofix])
values (1, 'A', 0),
       (2, 'B', 0)

insert into bla2 ([priority], [msg], [autofix])
values (1, 'A', 0),
       (2, 'B', 0)

insert into bla3 ([priority], [msg], [autofix])
values (1, 'A', 0),
       (2, 'B', 0)
;
declare @a nvarchar(max) = ''
declare @b nvarchar(max) = ''
declare @c nvarchar(max) = ''
declare @d nvarchar(max) = ''
declare @e nvarchar(max) = ''
declare @f nvarchar(max) = ''

-- I expect this to work and generate 'AB', but it doesn't
select @a = @a + [msg]
    from bla
    where   autofix = 0
    order by [priority] asc

-- this DOES work: convert nvarchar(4000)
select @b = @b + convert(nvarchar(4000),[msg])
    from bla
    where   autofix = 0
    order by [priority] asc

-- this DOES work: without WHERE clause
select @c = @c + [msg]
    from bla
    --where autofix = 0
    order by [priority] asc

-- this DOES work: without the order by
select @d = @d + [msg]
    from bla
    where   autofix = 0
    --order by [priority] asc

-- this DOES work: from bla2, so without the primary key on id
select @e = @e + [msg]
    from bla2
    where   autofix = 0
    order by [priority] asc

-- this DOES work: from bla3, so with msg nvarchar(1000) instead of nvarchar(max)
select @f = @f + [msg]
    from bla3
    where   autofix = 0
    order by [priority] asc

select @a as a, @b as b, @c as c, @d as d, @e as e, @f as f

Answer 1

TLDR; 这不是用于跨行连接字符串的记录/支持的方法。它有时有效，但有时会失败，因为这取决于您获得的执行计划。

而是使用以下有保证的方法之一

SQL Server 2017+

SELECT @a = STRING_AGG([msg], '') WITHIN GROUP (ORDER BY [priority] ASC)
FROM bla
where   autofix = 0

SQL Server 2005+

SELECT @a = (SELECT [msg] + ''
             FROM   bla
             WHERE  autofix = 0
             ORDER  BY [priority] ASC
             FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)') 

背景

KB article范德诺斯已经链接确实包括该行

The correct behavior for an aggregate concatenation query isundefined.

但随后提供了一种似乎确实表明确定性行为是可能的解决方法，从而使情况变得更加困惑。

In order to achieve the expected results from an aggregateconcatenation query, apply any Transact-SQL function or expression tothe columns in the SELECT list rather than in the ORDER BY clause.

有问题的查询不会将任何表达式应用于 ORDER BY 中的列条款。

2005年文章Ordering guarantees in SQL Server...是否说明

For backwards compatibility reasons, SQL Server provides support forassignments of type SELECT @p = @p + 1 ... ORDER BY at the top-mostscope.

在连接按预期工作的计划中，计算标量具有表达式 [Expr1003] = Scalar Operator([@x]+[Expr1004])出现在排序上方。

在无法工作的计划中，计算标量出现在排序下方。如 this connect item 中所述从 2006 年开始，表达式 @x = @x + [msg]出现在对每行进行评估的排序下方，但所有评估最终都使用预分配值 @x 。在 another similar Connect Item从 2006 年开始，微软的回应就提到“解决”这个问题。

Microsoft 对所有后续 Connect 项目中有关此问题(并且有很多)的回应都表明，这根本无法得到保证

Example 1

we do not make any guarantees on the correctness of concatenationqueries (like using variable assignments with data retrieval in aspecific order). The query output can change in SQL Server 2008depending on the plan choice, data in the tables etc. You shouldn'trely on this working consistently even though the syntax allows you towrite a SELECT statement that mixes ordered rows retrieval withvariable assignment.

Example 2

The behavior you are seeing is by design. Using assignment operations(concatenation in this example) in queries with ORDER BY clause hasundefined behavior. This can change from release to release or evenwithin a particular server version due to changes in the query plan.You cannot rely on this behavior even if there are workarounds. Seethe below KB article for more details:
http://support.microsoft.com/kb/287515 The ONLY guaranteedmechanism are the following:

1. Use cursor to loop through the rows in specific order and concatenate the values
2. Use for xml query with ORDER BY to generate the concatenated values
3. Use CLR aggregate (this will not work with ORDER BY clause)

Example 3

The behavior you are seeing is actually by design. This has to do withSQL being a set-manipulation language. All expressions in the SELECTlist (and this includes assignments too) are not guaranteed to beexecuted exactly once for each output row. In fact, SQL queryoptimizer tries hard to execute them as few times as possible. Thiswill give expected results when you are computing the value of thevariable based on some data in the tables, but when the value that youare assigning depends on the previous value of the same variable, theresults may be quite unexpected. If the query optimizer moves theexpression to a different place in the query tree, it may getevaluated less times (or just once, as in one of your examples). Thisis why we don't recommend using the "iteration" type assignments tocompute aggregate values. We find that XML-based workarounds ... usually work well for thecustomers

Example 4

Even without ORDER BY, we do not guarantee that @var = @var + will produce the concatenated value for any statementthat affects multiple rows. The right-hand side of the expression canbe evaluated either once or multiple times during query execution andthe behavior as I said is plan dependent.

Example 5

The variable assignment with SELECT statement is a proprietary syntax(T-SQL only) where the behavior is undefined or plan dependent ifmultiple rows are produced. If you need to do the string concatenationthen use a SQLCLR aggregate or FOR XML query based concatenation orother relational methods.