Concatenate records problem with order by

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

The following code

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?

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?

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

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

Attachment 154211


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

Attachment 154213

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

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

Attachment 154211

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

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

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

Quote:

---

Originally Posted by 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

---

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

Quote:

---

Originally Posted by szlamany

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:

Code:

---

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:

Attachment 154369

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

Attachment 154367

Here's what I found:

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

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 :).

Quote:

---

Originally Posted by CVMichael

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

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