3

I'm running the following query:

DECLARE @sql NVARCHAR(max) = N''
SELECT @sql += N' EXEC dbo.DoSomething @Tablename = ''' + SCHEMA_NAME(T.schema_id) + N'.' + T.name + N''''
--SELECT * 
FROM sys.tables T
INNER JOIN sys.indexes I ON I.object_id = T.object_id
ORDER by 1 
PRINT  @sql

But I'm only getting one line printed. If I take out the ORDER by 1 line, or replace it with
ORDER BY t.name then I get the expected result.

Is this a bug in SQL Server?
I've tried it on SQL SErver 2016 SP1 and 2014 Sp2-Cu1.

3
  • ORDER BY 1 and ORDER BY t.name do not do the same thing. – ypercubeᵀᴹ Nov 25 '16 at 10:09
  • @ypercubeᵀᴹ; I know that, but not that it affected the number of rows in my "Result Set" – Henrik Staun Poulsen Nov 25 '16 at 10:27
  • 1
    Yes, normally it should't affect. The problem here is a circular reference. The result of column 1 (@sql) is affected by the ORDER BY and the ORDER BY is affected by column 1. That's one reason why Martin (and the KB article) has in his answer that "The correct behavior for an aggregate concatenation query is undefined." – ypercubeᵀᴹ Nov 25 '16 at 10:29
6

Is this a bug in SQL Server?

No.

From PRB: Execution Plan and Results of Aggregate Concatenation Queries Depend Upon Expression Location

The correct behavior for an aggregate concatenation query is undefined.

See also this excellent answer by Martin Smith over at stackoverflow. nvarchar concatenation / index / nvarchar(max) inexplicable behavior

Sometimes it works and sometimes it does not work and that is dependent on the execution plan. The magic works if the compute scalar doing the concatenation is positioned as the first operator below the select operator.

declare @T table(Name nvarchar(5));
insert into @T values (N'aaaa3'),(N'bbbb2'),(N'cccc1');
declare @S nvarchar(18) = N'';

select @S += ',' + T.Name
from @T as T;

enter image description here

But if you are unlucky you could end up with a different plan. This one has a sort operator between the select and the compute scalar.

select @S += ',' + T.Name
from @T as T
order by reverse(T.Name);

enter image description here

The best way to solve this (in my opinion) is to use for xml path to do the concatenation instead.

select @S  = (
             select ',' + T.Name 
             from @T as T
             order by reverse(T.Name)
             for xml path(''), type
             ).value('text()[1]', 'nvarchar(max)');

Some version of SQL Server in the future will have a built in string aggregate function STRING_AGG (Transact-SQL).

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.