The most relevant documentation beinig this one: http://support.microsoft.com/kb/287515: Execution Plan and Results of Aggregate Concatenation Queries Depend Upon Expression Location. This article from Microsoft Support officially states (and even includes repro code to prove it) that this "aggregate concatenation" technique can have different results based on seemingly unrelated changes in the execution plan.

The weird thing in this article is that the "cause" section explicitly states that "the correct behavior for an aggregate concatenation query is undefined". However, then in the workaround section, it states that "in order to achieve the expected results from an aggregate concatenation query, (...)". So if the "correct behavior" is undefined, I would assume that the "expected results" are also undefined - and yet we are given a way to achieve those "expected results"?

I have so far been unable to find any other MS documentation describing what the expected results of such a query should be. Not even the "rather poorly documented" description that you apparently found. Do you perhaps have a link to that?

I have deliberately kept out of the discussion on performance, because I see no sense in measuring performance of a solution that is not guaranteed to return the correct results. If you are willing to sacrifice correctness for performance, then why not simply replace all your queries with [font="Courier New"]SELECT 42;[/font]? Probably also incorrect, but you'll never get faster than that! 😉

Anyway, your remark in another post that FOR XML concatenation requires de-entitizing the data, an often-overlooked bug in most posts that mention FOR XML. Whenever I use that method in my code, I always have to spend some time trying to retrieve that code. The reason why I referenced the FOR XML method in my posts but didn't give code examples is that I didn't feel like going on another Google spree when writing that. 🙂

But since you now brought if up, here is how I would write the code for this QotD:

SELECT STUFF((SELECT ', ' + productname

FROM dbo.Header AS h

FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 2, '');