Thanks for sharing David. I agree most CLR aggregates have not had much success beyond academics. I think part of this limitation is due to the lack of ability to perform an ORDER BY clause with an aggregate.

For example, lets say I create a CLR aggregate that concatenates strings together ie

SELECT dbo.List(myStringColumn,',') -- Where ',' means comma delimited

FROM myTable

Assuming I've implemented it correctly, this could produce output such as

firstRowValue,secondRowValue,thirdRowValue

or maybe parallelism kicks in (on a large set) and produces output where the order changes ie

secondRowValue,firstRowValue,thirdRowValue

Now, if we had we had the ability to perform the ORDER BY clause with the OVER clause ie

SELECT dbo.List(myStringColumn,',') OVER(ORDER BY myStringColumn)

FROM myTable

Then we could guarantee the correct (expected) order of output.

If you believe this would be a useful feature, and I do, please vote for it on connect.

https://connect.microsoft.com/SQLServer/feedback/details/254387/over-clause-enhancement-request-order-by-for-aggregates

In regards to the attribute IsInvariantToOrder, it's not supported, see the following link.

http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.server.sqluserdefinedaggregateattribute.isinvarianttoorder.aspx

Apparently, it will be implemented in the "future" which after 5 years hasn't been far enough into the future.