concatenação nvarchar / index / nvarchar (max) comportamento inexplicável

Eu hoje me deparei com um problema muito estranho no SQL Server (2008R2 e 2012). Eu estou tentando construir uma string usando concatenação em combinação com uma instrução select .

Eu encontrei soluções alternativas, mas eu realmente gostaria de entender o que está acontecendo aqui e por que isso não me dá o resultado esperado. Alguém pode explicar isso para mim?

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

A pedido, também o código aqui:

 -- 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 

O artigo da KB já vinculado pela VanDerNorth inclui a linha

O comportamento correto de uma consulta de concatenação agregada é indefinido.

mas, em seguida, passa a turvar as águas um pouco, fornecendo uma solução alternativa que parece indicar que o comportamento determinístico é possível.

Para obter os resultados esperados de uma consulta de concatenação agregada, aplique qualquer function ou expressão Transact-SQL às colunas na lista SELECT em vez de na cláusula ORDER BY.

Sua consulta problemática não aplica nenhuma expressão a colunas na cláusula ORDER BY .

O artigo de 2005 pedidos de garantia no SQL Server … faz estado

Por motivos de compatibilidade com versões anteriores, o SQL Server fornece suporte para atribuições do tipo SELECT @p = @p + 1 … ORDER BY no escopo superior.

Nos planos em que a concatenação funciona como você esperava, o escalar de cálculo com a expressão [Expr1003] = Scalar Operator([@x]+[Expr1004]) aparece acima da sorting.

No plano onde ele não funciona, o escalar de computação aparece abaixo da sorting. Como explicado neste item de conexão de 2006, quando a expressão @x = @x + [msg] aparece abaixo da sorting, ela é avaliada para cada linha, mas todas as avaliações acabam usando o valor de pré-atribuição de @x . Em outro item Connect similar de 2006, a resposta da Microsoft falou de “consertar” o problema.

A resposta da Microsoft em todos os itens posteriores do Connect sobre esse problema (e há muitos) declara que isso simplesmente não é garantido

Exemplo 1

Não garantimos a exatidão das consultas de concatenação (como o uso de atribuições de variables ​​com recuperação de dados em uma ordem específica). A saída da consulta pode mudar no SQL Server 2008, dependendo da escolha do plano, dados nas tabelas, etc. Você não deve confiar que isso funcione de forma consistente, embora a syntax permita escrever uma instrução SELECT que mescle a recuperação de linhas ordenadas com atribuição de variável.

Exemplo 2

O comportamento que você está vendo é por design. A utilização de operações de atribuição (concatenação neste exemplo) em consultas com a cláusula ORDER BY possui um comportamento indefinido. Isso pode mudar de release para release ou até mesmo dentro de uma versão específica do servidor devido a mudanças no plano de consulta. Você não pode confiar nesse comportamento mesmo se houver soluções alternativas. Veja o artigo da KB abaixo para mais detalhes:
http://support.microsoft.com/kb/287515 O ÚNICO mecanismo garantido é o seguinte:

  1. Use o cursor para percorrer as linhas em ordem específica e concatenar os valores
  2. Use para consulta xml com ORDER BY para gerar os valores concatenados
  3. Use o agregado CLR (isso não funcionará com a cláusula ORDER BY)

Exemplo 3

O comportamento que você está vendo é, na verdade, por design. Isso tem a ver com SQL sendo uma linguagem de manipulação de conjuntos. Todas as expressões na lista SELECT (e isso inclui atribuições também) não são garantidas para serem executadas exatamente uma vez para cada linha de saída. Na verdade, o otimizador de consultas SQL tenta realizá-las com a maior rapidez possível. Isso fornecerá resultados esperados quando você estiver computando o valor da variável com base em alguns dados nas tabelas, mas quando o valor que você está atribuindo depende do valor anterior da mesma variável, os resultados podem ser bastante inesperados. Se o otimizador de consulta mover a expressão para um local diferente na tree de consultas, ele poderá ser avaliado menos vezes (ou apenas uma vez, como em um dos seus exemplos). É por isso que não recomendamos o uso de atribuições do tipo “iteração” para calcular valores agregados. Descobrimos que as soluções baseadas em XML … geralmente funcionam bem para os clientes

Exemplo 4

Mesmo sem ORDER BY, não garantimos que @var = @var + produza o valor concatenado para qualquer instrução que afete várias linhas. O lado direito da expressão pode ser avaliado uma vez ou várias vezes durante a execução da consulta e o comportamento, como eu disse, depende do plano.

Exemplo 5

A atribuição de variables ​​com a instrução SELECT é uma syntax proprietária (somente T-SQL) onde o comportamento é indefinido ou depende do planejamento se várias linhas forem produzidas. Se você precisar fazer a concatenação de cadeia de caracteres, use um agregado SQLCLR ou concatenação baseada em consulta FOR XML ou outros methods relacionais.

Parece um pouco como este post: VARCHAR (MAX) agindo estranho ao concatenar string

A conclusão lá: Essa abordagem para a concatenação de strings geralmente funciona, mas não é garantida. A linha oficial no artigo da KB para um problema semelhante é que “O comportamento correto de uma consulta de concatenação agregada é indefinido”.