Hugo Kornelis (9/28/2012)
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.
Thanks for the reply; these are excellent points. I still read the KB article as specifically having to do with the ORDER BY issue, but I'm coming around to seeing your larger point. Today, the ORDER BY issue may be the only way to expose the problem with the "aggregate concatenation query" approach, but there's no guarantee that will be true tomorrow.
And as you point out, given that there's a perfectly sound alternative, we'd all certainly be wise to explore the XML method. That will improve my future code; whether I get time to refactor my existing stuff is another matter. :hehe: