Why does SQL Server ignore vaules in string concatenation when ORDER BY clause is specified

Question

I have the following table

Created Comment 2010/10/10 Text1 2010/11/11 Text2 2010/12/12 Text3 

I need to collect all comments in one line

 SELECT @Comment = COALESCE(@Comment, '') + CHAR(13) + CHAR(10) + CONVERT(NVARCHAR(30), [dbo].[Comment].[Created], 101) + ': ' + ISNULL([Comment].[Text], '') FROM Comment 

Without streamlining, it works as the expected end, returning all your comments. But after running the following code, where the ORDER BY clause is added:

 SELECT @Comment = COALESCE(@Comment, '') + CHAR(13) + CHAR(10) + CONVERT(NVARCHAR(30), [Created], 101) + ': ' + ISNULL([Text], '') FROM Comment ORDER BY Created 

Returns only the last comment. Does any body know why ORDER BY produces a strange result in concatenation?

PS: it works fine if instead of <concretization < FOR XML is used Is there a way to create a SQL Server function to combine multiple rows from a subquery into a single field with delimiters? .

Answer 1

Because you rely on undocumented behavior.

Microsoft says: "The correct behavior for requesting concatenation of aggregates is undefined. " If a computational scalar moves to the wrong place in the plan, it just stops working!