sql - Adding index to temporary table results in single row result set -

inside sp_executesql select statement used concatenate variable , value of column temporary table.

this gives weird results when apply index temporary table , use ordering in aforementioned select statement.

this happens when amount of rows of temporary table bigger 50 rows.

i don't pasting big code examples, not able reduce further.

if @maxjob >= 8, resulting @htmllog contains 67 characters. unexpected result.

if @maxjob < 8, resulting @htmllog contain more 67 characters. expected result.

furthermore,

when remove index idx_key #acl_log table, issue disappears when @maxjob >= 8. or when remove 'order [key] asc' @executesql_sql issue disappears well.

why?

declare @logtable varchar(max) set @logtable = '#acl_log'  if (object_id('[tempdb]..#acl_log')) not null     drop table #acl_log  create table #acl_log(     [key]       int             identity,     [message]   nvarchar(max)   not null,     index idx_key ([key]) )  declare @job int declare @maxjob int  set @job = 0 set @maxjob = 8  while (@job < @maxjob + 1) begin     insert #acl_log([message])     values         ('internet explorer running without add-ons')         ,('all internet explorer add-ons, such activex controls or toolbars, turned off. webpages might not display correctly.')         ,('to continue home page, click home button.')         ,('to browse using add-ons, close internet explorer , start again.')         ,('forward arrow  check latest windows updates. ')         ,('question icon how browser add-ons affect browsing experience? ')      set @job = @job + 1 end  declare @executesql_sql nvarchar(max) declare @executesql_param nvarchar(max)  declare @htmllog varchar(max) set @executesql_sql = '     set @htmllog = ''''     select @htmllog = @htmllog + [message]     ' + @logtable + '     order [key] asc' set @executesql_param = '@htmllog varchar(max) output'  exec master..sp_executesql @executesql_sql, @executesql_param, @htmllog = @htmllog output  select len(@htmllog), @htmllog 

the behavior of aggregate string concatenation undefined because plan-dependent according comment microsoft in this connect item:

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.

one workaround for xml clause:

declare @logtable varchar(max); set @logtable = '#acl_log';  if (object_id('[tempdb]..#acl_log')) not null     drop table #acl_log;  create table #acl_log(     [key]       int             identity,     [message]   nvarchar(max)   not null,     index idx_key ([key]) );  declare @job int; declare @maxjob int;  set @job = 0; set @maxjob = 8;  while (@job < @maxjob + 1) begin     insert #acl_log([message])     values         ('internet explorer running without add-ons')         ,('all internet explorer add-ons, such activex controls or toolbars, turned off. webpages might not display correctly.')         ,('to continue home page, click home button.')         ,('to browse using add-ons, close internet explorer , start again.')         ,('forward arrow  check latest windows updates. ')         ,('question icon how browser add-ons affect browsing experience? ');      set @job = @job + 1; end  declare @executesql_sql nvarchar(max); declare @executesql_param nvarchar(max);  declare @htmllog varchar(max); set @executesql_sql = '     set @htmllog = (select [message]     ' + @logtable + '     order [key]     xml path(''''), type).value(''.'', ''varchar(max)'');';  set @executesql_param = '@htmllog varchar(max) output';  exec master..sp_executesql @executesql_sql, @executesql_param, @htmllog = @htmllog output;  select len(@htmllog), @htmllog; go