Concatenación nvarchar/índice/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 concatenación en combinación con una selectdeclaración.
Descubrí que la cadena resultante solo contenía una de las cadenas de entrada, no un resultado concatenado de todas ellas como esperaba.
He encontrado soluciones, 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/#!18/d6228/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
TLDR; Este no es un enfoque documentado/compatible para concatenar cadenas entre filas. A veces funciona, pero también falla, ya que depende del plan de ejecución que obtenga.
En su lugar, utilice uno de los siguientes enfoques garantizados
Servidor SQL 2017+
SELECT @a = STRING_AGG([msg], '') WITHIN GROUP (ORDER BY [priority] ASC)
FROM bla
where autofix = 0
Servidor SQL 2005+
SELECT @a = (SELECT [msg] + ''
FROM bla
WHERE autofix = 0
ORDER BY [priority] ASC
FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)')
Hoy en día, la documentación advierte explícitamente contra el uso de Antipattern para la asignación de variables recursivas.
Fondo
El artículo de KB ya vinculado por VanDerNorth incluye la línea
El comportamiento correcto para una consulta de concatenación agregada no está definido.
pero luego enturbia un poco las aguas al proporcionar una solución alternativa que parece indicar que es posible un comportamiento determinista.
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 de la lista SELECT en lugar de a la cláusula ORDER BY.
Su consulta problemática no aplica ninguna expresión a las columnas de la ORDER BYcláusula.
El artículo de 2005 Garantías de pedidos en SQL Server... afirma
Por razones 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 planes donde la concatenación funciona como esperaba, el escalar de cálculo con la expresión [Expr1003] = Scalar Operator([@x]+[Expr1004])aparece encima de la clasificación.
En el plan donde no funciona, el escalar de cálculo aparece debajo de la clasificación. Como se explica en este elemento de conexión de 2006, cuando la expresión @x = @x + [msg]aparece debajo de la clasificación, se evalúa para cada fila, pero todas las evaluaciones terminan usando el valor de asignación previa de @x. En otro Connect Item similar de 2006, la respuesta de Microsoft hablaba de "solucionar" el problema.
La respuesta de Microsoft en todos los elementos posteriores de Connect sobre este problema (y hay muchos) afirma que esto simplemente no está garantizado.
Ejemplo 1
No ofrecemos ninguna garantía sobre la exactitud de las consultas de concatenación (como el uso de 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 a pesar de que la sintaxis le permite escribir una instrucción SELECT que combine la recuperación de filas ordenadas con la asignación de variables.
Ejemplo 2
El comportamiento que está 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 particular del servidor debido a cambios en el plan de consulta. No puede confiar en este comportamiento incluso si existen soluciones. Consulte el siguiente artículo de KB para obtener más detalles:
http://support.microsoft.com/kb/287515 El ÚNICO mecanismo garantizado es el siguiente:
- Utilice el cursor para recorrer las filas en un orden específico y concatenar los valores
- Úselo para consultas xml con ORDER BY para generar los valores concatenados
- Utilice el agregado CLR (esto no funcionará con la cláusula ORDER BY)
Ejemplo 3
El comportamiento que está 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 también incluye las asignaciones) se ejecuten exactamente una vez para cada fila de salida. De hecho, el optimizador de consultas SQL se esfuerza por ejecutarlas el menor número de veces posible. Esto dará los resultados esperados cuando calcule el valor de la variable en función de algunos datos de las tablas, pero cuando el valor que asigne dependa 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, es posible que se evalúe menos veces (o solo una vez, como en uno de sus ejemplos). Es por eso que no recomendamos utilizar asignaciones de tipo "iteración" para calcular valores agregados. Descubrimos que las soluciones basadas en XML... normalmente funcionan bien para los clientes.
Ejemplo 4
Incluso sin ORDER BY, no garantizamos que @var = @var + produzca el valor concatenado para cualquier declaració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 variables con la instrucción SELECT es una sintaxis propietaria (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 FOR XML u otros métodos relacionales.
Se parece un poco a esta publicación: VARCHAR(MAX) actúa de manera extraña al concatenar cadenas
La conclusión es que este enfoque para la concatenación de cadenas suele funcionar, 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".
