BowlOfCereal (9/28/2012)
There's been a lot of discussion in this (oddly) revived thread today.
The reason for the revival of the thread is that the article was prominently featured in the newsletter.
According to Microsoft's KB article and Connect comments: the unpredictable, unsupported behavior occurs "when you apply any operators or expressions to the ORDER BY clause of aggregate concatenation queries". I understand that (or at least I think I do). But this has nothing to do with building a comma delimited string. The original article said nothing about applying operators to an ORDER BY clause.
So, my question: is it true that the method presented in this article is "known to potentially return incorrect results"?
From the relevant Microsoft Knowledge Base article: "The correct behavior for an aggregate concatenation query is undefined."
If the correct behaviour is undefined, then it's impopssible to tell if a given result is incorrect. Ergo, the method presented in this article can by definition never return "incorrect" results.
However, the results returned may be completely different than what you expect, or want. For instance, when the optimizer chooses to use a parallel plan, it could just return the result from one of the threads, which you would probably consider incorrect. I don't think that the optimizer will at this time choose a parallel plan for queries of this type (I just spent a half hour trying very hard to get it to, but failed) - but since this is undocumented behaviour of the optimizer, that might change. And if a future change to the optimizer causes it to create a parallel plan for this query, your bug reports will probably be closed as "by design" - since "the correct behavior for an aggregate concatenation query is undefined."
For me, this is enough reason to avoid this method. The XML method is a perfect replacement - and this method IS documented, and hence guaranteed.