Nvarchar concatenation/index/nvarchar (max) необъяснимое поведение

Сегодня я столкнулся с действительно странной проблемой в SQL Server (как 2008R2, так и 2012). Я пытаюсь создать строку с использованием конкатенации в сочетании с оператором select.

Я нашел обходные пути, но мне бы очень хотелось понять, что здесь происходит и почему это не дает мне ожидаемого результата. Может кто-нибудь объяснить это мне?

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

По запросу также код здесь:

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

Ответы

Ответ 1

статья KB, уже связанная с VanDerNorth, включает в себя строку

Правильное поведение для запроса совокупной конкатенации undefined.

но затем переходит к мутной воде немного, предоставляя обходное решение, которое, как представляется, указывает на детерминированное поведение.

Чтобы достичь ожидаемых результатов от совокупности запрос конкатенации, применяйте любую функцию 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. В другом аналогичном элементе подключения от 2006 года ответ от Microsoft говорил об "исправлении" проблемы.

Ответ Microsoft на все более поздние элементы Connect в этой проблеме (и их много) утверждают, что это просто не гарантируется

Пример 1

мы не делаем никаких гарантий относительно правильности конкатенации запросов (например, с помощью присвоений переменных с извлечением данных в конкретный заказ). Выход запроса может измениться в SQL Server 2008 в зависимости от выбора плана, данных в таблицах и т.д. Вы не должны полагайтесь на это, работая последовательно, хотя синтаксис позволяет вам написать инструкцию SELECT, которая смешивает упорядоченное упорядочение строк с помощью назначение переменной.

Пример 2

Поведение, которое вы видите, - это дизайн. Использование операций присваивания (конкатенация в этом примере) в запросах с предложением ORDER BY имеет undefined. Это может измениться с момента выпуска до выпуска или даже в пределах определенной версии сервера из-за изменений в плане запроса. Вы не можете полагаться на это поведение, даже если есть обходные пути. Видеть ниже статья KB для более подробной информации:
http://support.microsoft.com/kb/287515 ТОЛЬКО гарантировано механизм:

  • Используйте курсор для прокрутки строк в определенном порядке и объединения значений
  • Использовать для запроса xml с ORDER BY для генерации конкатенированных значений
  • Использовать агрегат CLR (это не будет работать с предложением ORDER BY)

Пример 3

Поведение, которое вы видите, на самом деле по дизайну. Это связано с SQL является языком манипуляции множеством. Все выражения в SELECT список (и это включает в себя также задания), не гарантируется выполняется ровно один раз для каждой выходной строки. Фактически, SQL-запрос Оптимизатор пытается выполнить их как можно меньше. Эта даст ожидаемые результаты, когда вы вычисляете значение переменная, основанная на некоторых данных в таблицах, но когда значение, которое вы назначаются, зависит от предыдущего значения той же переменной, результаты могут быть довольно неожиданными. Если оптимизатор запросов перемещает выражение в другое место в дереве запросов, оно может получить оценивается меньше времени (или только один раз, как в одном из ваших примеров). Эта поэтому мы не рекомендуем использовать назначения типа "итерация" для вычислить совокупные значения. Мы обнаруживаем, что обходные методы на основе XML... обычно хорошо работают для клиенты

Пример 4

Даже без ORDER BY мы не гарантируем, что @var = @var +  будет производить конкатенированное значение для любого заявления который влияет на несколько строк. Правая часть выражения может оцениваться один или несколько раз во время выполнения запроса и поведение, как я сказал, зависит от плана.

Пример 5

Назначение переменной с помощью оператора SELECT является проприетарным синтаксисом (Только для T-SQL), где поведение undefined или зависит от плана, если создается несколько строк. Если вам нужно выполнить конкатенацию строк затем используйте компиляцию SQLCLR или FOR XML на основе конкатенации или другие реляционные методы.

Ответ 2

Похоже на это сообщение: VARCHAR (MAX) действует странно при конкатенации строки

Вывод: Такой подход к конкатенации строк обычно работает, но он не гарантируется. Официальная строка в статье KB для аналогичной проблемы заключается в том, что "Правильное поведение для запроса суммарной конкатенации - undefined".