Conducta nvarchar / index / nvarchar (max) comportamiento inexplicable

Hoy me encontré con un problema realmente extraño en SQL Server (tanto 2008R2 como 2012). Estoy intentando construir una cadena usando la concatenación en combinación con una statement de select .

He encontrado soluciones alternativas, pero realmente me gustaría entender qué está pasando aquí y por qué no me da el resultado esperado. ¿Alguien puede explicar me lo?

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

A petición, también el código aquí:

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

El artículo de KB ya enlazado por VanDerNorth incluye la línea

El comportamiento correcto para una consulta de concatenación agregada no está definido.

pero luego continúa enturbiando un poco las aguas proporcionando una solución alternativa que parece indicar que el comportamiento determinista es posible.

Para lograr los resultados esperados de una consulta de concatenación agregada, aplique cualquier función o expresión de Transact-SQL a las columnas en la lista SELECCIONAR en lugar de en la cláusula ORDER BY.

Su consulta problemática no aplica ninguna expresión a las columnas en la cláusula ORDER BY .

El artículo de 2005 Garantías de pedido en SQL Server … sí indica

Por motivos de compatibilidad con versiones anteriores, SQL Server proporciona soporte para asignaciones de tipo SELECT @p = @p + 1 … ORDER BY en el ámbito superior.

En los planos donde la concatenación funciona como esperaba, el escalar de cálculo con la expresión [Expr1003] = Scalar Operator([@x]+[Expr1004]) aparece encima del ordenamiento.

En el plan donde no funciona, el escalar de cómputo aparece debajo del ordenamiento. Como se explica en este artículo de conexión de 2006, cuando la expresión @x = @x + [msg] aparece debajo del orden en que se evalúa para cada fila, todas las evaluaciones terminan usando el valor de @x de @x . En otro elemento de conexión similar de 2006, la respuesta de Microsoft habló de “solucionar” el problema.

La respuesta de Microsoft en todos los elementos de conexión posteriores sobre este tema (y hay muchos) indica que esto simplemente no está garantizado

Ejemplo 1

no garantizamos la exactitud de las consultas de concatenación (como utilizar asignaciones de variables con recuperación de datos en un orden específico). El resultado de la consulta puede cambiar en SQL Server 2008 dependiendo de la elección del plan, los datos en las tablas, etc. No debe confiar en que esto funcione de manera consistente aunque la syntax le permita escribir una instrucción SELECT que mezcle la recuperación de filas ordenadas con la asignación de variables.

Ejemplo 2

El comportamiento que estás viendo es por diseño. El uso de operaciones de asignación (concatenación en este ejemplo) en consultas con la cláusula ORDER BY tiene un comportamiento indefinido. Esto puede cambiar de una versión a otra o incluso dentro de una versión de servidor particular debido a cambios en el plan de consulta. No puede confiar en este comportamiento incluso si hay soluciones provisionales. Consulte el artículo de KB a continuación para obtener más detalles:
http://support.microsoft.com/kb/287515 El ÚNICO mecanismo garantizado es el siguiente:

  1. Use el cursor para recorrer las filas en orden específico y concatenar los valores
  2. Use para la consulta xml con ORDER BY para generar los valores concatenados
  3. Utilice el agregado de CLR (esto no funcionará con la cláusula ORDER BY)

Ejemplo 3

El comportamiento que estás viendo es en realidad por diseño. Esto tiene que ver con que SQL es un lenguaje de manipulación de conjuntos. No se garantiza que todas las expresiones en la lista SELECT (y esto incluye asignaciones también) se ejecuten exactamente una vez para cada fila de salida. De hecho, el optimizador de consultas SQL intenta ejecutarlos las veces que sea posible. Esto dará los resultados esperados cuando se calcula el valor de la variable en función de algunos datos en las tablas, pero cuando el valor que se asigna depende del valor anterior de la misma variable, los resultados pueden ser bastante inesperados. Si el optimizador de consultas mueve la expresión a un lugar diferente en el árbol de consultas, se puede evaluar menos veces (o solo una vez, como en uno de sus ejemplos). Es por eso que no recomendamos utilizar las asignaciones de tipo “iteración” para calcular valores agregados. Encontramos que las soluciones basadas en XML … generalmente funcionan bien para los clientes

Ejemplo 4

Incluso sin ORDER BY, no garantizamos que @var = @var + producirá el valor concatenado para cualquier instrucción que afecte a varias filas. El lado derecho de la expresión se puede evaluar una o varias veces durante la ejecución de la consulta y el comportamiento, como dije, depende del plan.

Ejemplo 5

La asignación de variable con la instrucción SELECT es una syntax patentada (solo T-SQL) donde el comportamiento no está definido o depende del plan si se producen varias filas. Si necesita realizar la concatenación de cadenas, utilice un agregado SQLCLR o una concatenación basada en consultas XML u otros métodos relacionales.

Parece un poco como esta publicación: VARCHAR (MAX) actúa raro cuando concatena cadena

La conclusión allí: este enfoque de la concatenación de cadenas generalmente funciona, pero no está garantizado. La línea oficial en el artículo de KB para un problema similar es que “el comportamiento correcto para una consulta de concatenación agregada no está definido”.

    Intereting Posts