Конкатенация nvarchar / index / nvarchar(max) необъяснимое поведение
Сегодня я столкнулся с действительно странной проблемой в SQL Server (как 2008R2, так и 2012). Я пытаюсь создать строку с использованием конкатенации в сочетании с select заявление.
Я нашел обходные пути, но мне бы очень хотелось понять, что здесь происходит и почему это не дает мне ожидаемого результата. Может кто-нибудь объяснить это мне?
По запросу также код здесь:
-- 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
2 ответа
Статья КБ, уже связанная VanDerNorth, включает в себя строку
Правильное поведение для запроса совокупной конкатенации не определено.
но затем немного мутит воду, предлагая обходной путь, который, по-видимому, указывает на то, что детерминированное поведение возможно.
Чтобы получить ожидаемые результаты от запроса агрегированной конкатенации, примените любую функцию или выражение Transact-SQL к столбцам в списке SELECT, а не в предложении ORDER BY.
Ваш проблемный запрос не применяет никаких выражений к столбцам в ORDER BY пункт.
Статья 2005 г. Упорядочение гарантий в SQL Server... действительно ли
По причинам обратной совместимости SQL Server обеспечивает поддержку назначений типа SELECT @p = @p + 1 ... ORDER BY в самой верхней области.
В планах, где конкатенация работает так, как вы ожидали, вычислите скаляр с выражением [Expr1003] = Scalar Operator([@x]+[Expr1004]) появляется над сортировкой.
В плане, где он не работает, вычисляемый скаляр появляется ниже сортировки. Как объяснено в этом пункте подключения с 2006 года, когда выражение @x = @x + [msg] отображается под сортировкой, которая оценивается для каждой строки, но все оценки заканчиваются использованием значения предварительного присваивания @x, В другом подобном Connect Item от 2006 года в ответе Microsoft говорилось о "исправлении" проблемы.
В ответе Microsoft на все последующие элементы Connect по этой проблеме (и их много) говорится, что это просто не гарантируется.
мы не даем никаких гарантий относительно правильности запросов на конкатенацию (например, использование переменных присваивания с извлечением данных в определенном порядке). Вывод запроса может изменяться в SQL Server 2008 в зависимости от выбора плана, данных в таблицах и т. Д. На эту работу не следует полагаться согласованно, даже если синтаксис позволяет написать инструкцию SELECT, которая смешивает извлечение упорядоченных строк с назначением переменных.
Поведение, которое вы видите, является дизайном. Использование операций присваивания (в данном примере конкатенации) в запросах с предложением ORDER BY имеет неопределенное поведение. Это может измениться от выпуска к выпуску или даже в пределах определенной версии сервера из-за изменений в плане запроса. Вы не можете полагаться на это поведение, даже если есть обходные пути. См. Ниже статью базы знаний для более подробной информации:
http://support.microsoft.com/kb/287515 ЕДИНСТВЕННЫМ гарантированным механизмом являются следующие:
- Используйте курсор, чтобы перебрать строки в определенном порядке и объединить значения
- Используйте для запроса XML с ORDER BY, чтобы сгенерировать объединенные значения
- Использовать агрегат CLR (это не будет работать с предложением ORDER BY)
Поведение, которое вы видите, на самом деле задумано. Это связано с тем, что SQL является языком манипулирования множествами. Все выражения в списке SELECT (и это включает в себя также присваивания) не обязательно выполняются ровно один раз для каждой выходной строки. Фактически, оптимизатор SQL-запросов старается выполнить их как можно меньше раз. Это даст ожидаемые результаты, когда вы вычисляете значение переменной на основе некоторых данных в таблицах, но когда назначаемое вами значение зависит от предыдущего значения той же переменной, результаты могут быть довольно неожиданными. Если оптимизатор запросов перемещает выражение в другое место в дереве запросов, оно может оцениваться меньше раз (или только один раз, как в одном из ваших примеров). Вот почему мы не рекомендуем использовать присваивания типа "итерация" для вычисления совокупных значений. Мы находим, что обходные пути на основе XML... обычно хорошо работают для клиентов
Даже без ORDER BY мы не гарантируем, что @var = @var + создаст объединенное значение для любого оператора, который влияет на несколько строк. Правая часть выражения может быть оценена либо один, либо несколько раз во время выполнения запроса, и поведение, как я сказал, зависит от плана.
Присвоение переменной с оператором SELECT является проприетарным синтаксисом (только T-SQL), где поведение не определено или зависит от плана, если создается несколько строк. Если вам нужно выполнить конкатенацию строк, используйте агрегирование SQLCLR или конкатенацию на основе запросов FOR XML или другие реляционные методы.
Похоже на этот пост: VARCHAR (MAX) ведет себя странно при конкатенации строки
Отсюда вывод: этот подход к конкатенации строк обычно работает, но он не гарантирован. Официальная строка в статье базы знаний по похожей проблеме: "Правильное поведение для запроса совокупной конкатенации не определено".