Hugo Kornelis (6/25/2010)

---

Though the explanation sounds enlightening, it is wrong. And it is also wrong to claim that the result of this query will always be "C".

Though many people seem to prefer to ignore it, it is widely known that this method of string concatenation is NOT SUPPORTED. Microsoft itself writes (I believe in a Knowledge Base article; I've spent some time trying to hunt it down but failed, unfortunately) that this method is not supported and that the results are unpredictable.

As far as I am concerned, any of the alternatives mentioned may be returned, depending on version, SP level and build of SQL Server, hardware used, and amount of other activity on the server.

Please, people - NEVER USE CODE LIKE THIS IN PRODUCTION CODE!!!!

Microsoft somehwere in 2008

---

Thanks for your feedback. The behavior you are seeing is by design. Using assignment operations (concatenation in this example) in queries with ORDER BY clause has undefined behavior. This can change from release to release or even within a particular server version due to changes in the query plan. You cannot rely on this behavior even if there are workarounds. See the below KB article for more details:

http://support.microsoft.com/kb/287515

The ONLY guaranteed mechanism are the following:

1. Use cursor to loop through the rows in specific order and concatenate the values

2. Use for xml query with ORDER BY to generate the concatenated values

3. Use CLR aggregate (this will not work with ORDER BY clause)

--

Umachandar, SQL Programmability Team