Concatenate records problem with order by

[CVMichael]

This is a weird problem that is happening only on some SQL servers.

The following code

declare @ServerApps varchar(max)
declare @apps table(AppName varchar(100))

insert @apps(AppName) values('Application A')
insert @apps(AppName) values('Application B')
insert @apps(AppName) values('Application C')

select @ServerApps = ISNULL(@ServerApps, '') + '/' + AppName from @apps
--order by AppName

select @ServerApps

returns: "/Application A/Application B/Application C"

BUT, if I uncomment "order by AppName", then on some servers I get the same result, but other server I only get "/Application C"

First I thought it's the server version, but then I tried on 2 different SQL Server 2012, and on one I got the full strings concatenated, and the other one I only got "/Application C"

It only happens when there is an ORDER BY

Does anyone know what could be wrong? any settings in SQL Server that makes it behave like this?

Can you guys try it on your SQL Servers? what do you get?

[szlamany]

I just tried it on 4 different SQL boxes - all worked fine for me both with and without the ORDER BY

Show the execution plan for both the working and non-working run on system. Do they look any different?

[CVMichael]

The difference between the execution plan is the order the sort is done:

On the server that returns the correct result (all records concatenated):




And on the server that returns only one record (the last record):

[GBeats]

what are the results and execution plans from both servers when you comment out the 'ORDER BY' part?

[CVMichael]

The result without ORDER BY is on both servers return all records concatenated, and execution plan is:

[CVMichael]

It's been a week since I posted my question. No one has any clue on what's going on?

[techgnome]

Honestly, I'm surprised it works period. When ever I've tried to order by an alias like that, I get errors.
The only thing that comes to mind it to check the collation on servers, and databases, and maybe also check the Compatibility Options. Easily any of those could cause differences in the results.

-tg

[szlamany]

Change it from this:

select @ServerApps = ISNULL(@ServerApps, '') + '/' + AppName from @apps

to this:

select @ServerApps = ISNULL(@ServerApps, '') + '/' + IsNull(AppName,'Found a NULL in the APPNAME') from @apps

[CVMichael]

Honestly, I'm surprised it works period. When ever I've tried to order by an alias like that, I get errors.
The only thing that comes to mind it to check the collation on servers, and databases, and maybe also check the Compatibility Options. Easily any of those could cause differences in the results.

-tg

Collation and Compatibility Options seem to be the same on both servers.

Change it ...to this:
select @ServerApps = ISNULL(@ServerApps, '') + '/' + IsNull(AppName,'Found a NULL in the APPNAME') from @apps

Didn't make any difference since there are no NULLs

But, just for the fun of it, I tried to put a TOP 100, and now on the server that before it was returning incorrect (only one row), now it returns ALL rows concatenated!

The execution plan is different, even though both servers return the same thing (when using top 100 with ORDER BY)

The query:

declare @ServerApps varchar(max)
declare @apps table(AppName varchar(100))

insert @apps(AppName) SELECT 'Application A' UNION ALL
SELECT 'Application B' UNION ALL
SELECT 'Application C'

select TOP 100 @ServerApps = ISNULL(@ServerApps, '') + '/' + AppName from @apps
order by AppName

select @ServerApps

This is the server that returns incorrect (one row), when not using TOP 100 with order by:



and this is the server that returns all rows regardless if I have ORDER BY:

[CVMichael]

Here's what I found:

https://marc.durdin.net/2015/07/conc...our-by-design/

[GBeats]

Great find CVMichael....

i personally never came accross this method of concatenation and was intrigued at how it works, it seems though that its got as many problems as it solves .

[szlamany]

Here's what I found:

Wow - solid google skills dude!

@gbeats - I've used simple concatenation with a STRING variable in a SELECT statement on many, many occasions. Never had issue...

[Ecniv]

From a limited configuration experience with MS Sql, perhaps the one server has slightly different settings.
Perhaps ask szlamany whether there is a way to copy the configurations from a working server and update the one that is not working properly - without needing to re-install etc
If there is a way to do this you could copy the two configuration files and compare them in say notepad++ to see where the differences are...?