SQL user defined aggregate order of values preserved?

Im using the code from this MSDN page to create a user defined aggregate to concatenate strings with group by's in SQL server. One of my requirements is that the order of the concatenated values are the same as in the query. For example:

Value   Group
1       1
2       1
3       2
4       2

Using query

SELECT
  dbo.Concat(tbl.Value) As Concat,
  tbl.Group
FROM
  (SELECT TOP 1000
     tblTest.*
  FROM 
    tblTest
  ORDER BY 
    tblTest.Value) As tbl
GROUP BY
  tbl.Group

Would result in:

Concat  Group
"1,2"   1
"3,4"   2

The result seems to always come out correct and as 开发者_开发技巧expected, but than I came across this page that states that the order is not guaranteed and that attribute SqlUserDefinedAggregateAttribute.IsInvariantToOrder is only reserved for future use.

So my question is: Is it correct to assume that the concatenated values in the string can end up in any order?

If that is the case then why does the example code on the MSDN page use the IsInvariantToOrder attribute?

---

I suspect a big problem here is your statement "the same as in the query" - however, your query never defines (and cannot define) an order by the things being aggregated (you can of course order the groups, by having a ORDER BY after the GROUP BY). Beyond that, I can only say that it is based purely on a set (rather than an ordered sequence), and that technically the order is indeed undefined.

---

While the accepted answer is correct, I wanted to share a workaround that others may find useful. Warning: it involves not using a user-defined aggregate at all :)

The link below describes an elegant way to build a concatenated, delimited list using only a SELECT statement and a varchar variable. The upside (for this thread) is that you can specify the order in which the rows are processed. The downside is that you can't easily concatenate across many different subsets of rows without painful iteration.

Not perfect, but for my use case was a good workaround.

http://blog.sqlauthority.com/2008/06/04/sql-server-create-a-comma-delimited-list-using-select-clause-from-table-column/