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
Assuming I've implemented it correctly, this could produce output such as
or maybe parallelism kicks in (on a large set) and produces output where the order changes ie
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)
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.