nvarchar串联/索引/nvarchar(Max)令人费解的行为

Question

nvarchar串联/索引/nvarchar(Max)令人费解的行为

我今天在SQLServer中遇到了一个非常奇怪的问题(2008R2和2012)。我试图使用连接与select声明。

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

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

应要求，这里的代码也是：

-- base tablecreate table bla (
    [id] int identity(1,1) primary key,
    [priority] int,
    [msg] nvarchar(max),
    [autofix] bit)-- table without primary key on id columncreate table bla2 (
    [id] int identity(1,1),
    [priority] int,
    [msg] nvarchar(max),
    [autofix] bit)-- table with nvarchar(1000) instead of maxcreate table bla3 (
    [id] int identity(1,1) primary key,
    [priority] int,
    [msg] nvarchar(1000),
    [autofix] bit)-- fill the three tables with the same valuesinsert 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'tselect @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 clauseselect @c = @c + [msg]
    from bla    --where autofix = 0
    order by [priority] asc-- this DOES work: without the order byselect @d = @d + [msg]
    from bla    where   autofix = 0
    --order by [priority] asc-- this DOES work: from bla2, so without the primary key on idselect @e = @e + [msg]

Answer 1

这个KB文章由VanDerNorth链接的已经包含了行未定义聚合连接查询的正确行为。但接下来，通过提供一个似乎确实表明确定性行为是可能的解决方案，把水弄得有点浑浊。为了实现聚合连接查询的预期结果，请将任何Transact-SQL函数或表达式应用于SELECT列表中的列，而不是ORDERBY子句中的列。有问题的查询不会将任何表达式应用于ORDER BY条款。2005年文章SQL Server中的订单保证。状态出于向后兼容性的原因，SQLServer提供了对SELECT@p=@p+1类型赋值的支持.在最高的范围内订购。在连接工作的计划中，与表达式一起计算标量。[Expr1003] = Scalar Operator([@x]+[Expr1004])出现在该类别的上方。在无法工作的计划中，计算标量出现在排序下面。如在此连接项从2006年开始@x = @x + [msg]显示在为每一行计算的排序下面，但所有计算结果都使用@x..在……里面另一个类似的连接项从2006年起，微软的回应就谈到“解决”这个问题。Microsoft对有关此问题的所有后续连接项的响应(还有许多)指出，这一点根本无法保证例1我们不保证连接查询的正确性(比如使用变量赋值和特定顺序的数据检索)。SQL Server 2008中的查询输出可以根据计划选择、表中的数据等进行更改。即使语法允许您编写一个SELECT语句，将有序的行检索与变量赋值混合在一起，您也不应该一直依赖这种工作。例2你所看到的行为是故意的。在带有ORDERBY子句的查询中使用赋值操作(在本例中为串联)具有未定义的行为。由于查询计划的更改，这可能在不同版本之间发生更改，甚至在特定的服务器版本中也会发生变化。即使有解决办法，也不能依赖这种行为。有关更多细节，请参见下面的KB文章：http://support.microsoft.com/kb/287515唯一的保障机制如下：使用游标按特定顺序遍历行，并将值连接起来。用于带有Orderby的xml查询，以生成级联的值使用CLR聚合(这不适用于ORDERBY子句)例3你所看到的行为实际上是故意的。这与SQL是一种集操作语言有关。SELECT列表中的所有表达式(这也包括赋值)不能保证对每个输出行精确执行一次。实际上，SQL查询优化器试图尽可能少地执行它们。当您根据表中的某些数据计算变量的值时，这将给出预期的结果，但是当您要分配的值取决于同一变量的前一个值时，结果可能是非常出乎意料的。如果查询优化器将表达式移动到查询树中的不同位置，则可能会得到较少的计算次数(或者只得到一次，如您的一个示例所示)。这就是为什么我们不建议使用“迭代”类型赋值来计算聚合值的原因。我们发现基于XML的解决方案.通常为客户服务。例4即使没有ORDERBY，我们也不能保证@var=@var+将为任何影响多行的语句生成级联值。表达式的右侧可以在查询执行期间计算一次或多次，而且正如我所说的行为依赖于计划。例5带有SELECT语句的变量赋值是一种专有语法(仅为T-SQL)，在这种语法中，行为是未定义的，如果产生多个行，则计划依赖。如果需要进行字符串连接，则使用SQLCLR聚合或用于基于XML查询的连接或其他关系方法。