sql server 2012 - nvarchar concatenation / index / nvarchar(max) inexplicable behavior -

i today ran weird problem in sql server (both 2008r2 , 2012). i'm trying build string using concatenation in combination select statement.

i have found workarounds, understand what's going on here , why doesn't give me expected result. can explain me?

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

on request, code here:

-- 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 nvarchar(1000) instead of max create table bla3 (     [id] int identity(1,1) primary key,     [priority] int,     [msg] nvarchar(1000),     [autofix] bit )  -- fill 3 tables same values insert bla ([priority], [msg], [autofix]) values (1, 'a', 0),        (2, 'b', 0)  insert bla2 ([priority], [msg], [autofix]) values (1, 'a', 0),        (2, 'b', 0)  insert 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) = ''  -- expect work , generate 'ab', doesn't select @a = @a + [msg]     bla       autofix = 0     order [priority] asc  -- work: convert nvarchar(4000) select @b = @b + convert(nvarchar(4000),[msg])     bla       autofix = 0     order [priority] asc  -- work: without clause select @c = @c + [msg]     bla     --where autofix = 0     order [priority] asc  -- work: without order select @d = @d + [msg]     bla       autofix = 0     --order [priority] asc  -- work: bla2, without primary key on id select @e = @e + [msg]     bla2       autofix = 0     order [priority] asc  -- work: bla3, msg nvarchar(1000) instead of nvarchar(max) select @f = @f + [msg]     bla3       autofix = 0     order [priority] asc  select @a a, @b b, @c c, @d d, @e e, @f f 

the kb article linked vandernorth include line

the correct behavior aggregate concatenation query undefined.

but goes on muddy waters bit providing workaround seem indicate deterministic behaviour possible.

in order achieve expected results aggregate concatenation query, apply transact-sql function or expression columns in select list rather in order clause.

your problematic query not apply expressions columns in order by clause.

the 2005 article ordering guarantees in sql server... state

for backwards compatibility reasons, sql server provides support assignments of type select @p = @p + 1 ... order @ top-most scope.

in plans concatenation works expected compute scalar expression [expr1003] = scalar operator([@x]+[expr1004]) appears above sort.

in plan fails work compute scalar appears below sort. explained in this connect item 2006 when expression @x = @x + [msg] appears below sort evaluated each row evaluations end using pre assignment value of @x. in another similar connect item 2006 response microsoft spoke of "fixing" issue.

the microsoft response on later connect items on issue (and there many) state not guaranteed

example 1

we not make guarantees on correctness of concatenation queries (like using variable assignments data retrieval in specific order). query output can change in sql server 2008 depending on plan choice, data in tables etc. shouldn't rely on working consistently though syntax allows write select statement mixes ordered rows retrieval variable assignment.

example 2

the behavior seeing design. using assignment operations (concatenation in example) in queries order clause has undefined behavior. can change release release or within particular server version due changes in query plan. cannot rely on behavior if there workarounds. see below kb article more details:
http://support.microsoft.com/kb/287515 guaranteed mechanism following:

1. use cursor loop through rows in specific order , concatenate values
2. use xml query order generate concatenated values
3. use clr aggregate (this not work order clause)

example 3

the behavior seeing design. has sql being set-manipulation language. expressions in select list (and includes assignments too) not guaranteed executed once each output row. in fact, sql query optimizer tries hard execute them few times possible. give expected results when computing value of variable based on data in tables, when value assigning depends on previous value of same variable, results may quite unexpected. if query optimizer moves expression different place in query tree, may evaluated less times (or once, in 1 of examples). why don't recommend using "iteration" type assignments compute aggregate values. find xml-based workarounds ... work customers

example 4

even without order by, not guarantee @var = @var + produce concatenated value statement affects multiple rows. right-hand side of expression can evaluated either once or multiple times during query execution , behavior said plan dependent.

example 5

the variable assignment select statement proprietary syntax (t-sql only) behavior undefined or plan dependent if multiple rows produced. if need string concatenation use sqlclr aggregate or xml query based concatenation or other relational methods.