Custom Aggregate Functions in MS SQL Server?

Question

How can I create a custom aggregate function in MS SQL Server? An example would help a lot.

Answer 1

SQL Server 2000 doesn't officially support custom aggregate functions. However, I recently needed that functionality as well, and I found this article enlightening:
http://weblogs.sqlteam.com/jeffs/articles/1490.aspx

It's a bit hack-ish, though: it requires access to the sp_OA___ extended procedures.

The summary is that you can simulate an aggregate function with a series of four wrapper functions, each of which performs one of the following tasks:

1. Create an ActiveX object that can hold state within the query. Call this before running the query.
   • Do the actual aggregation using the ActiveX object.
   • Clear the ActiveX object state on GROUP BY boundries
   • Destroy the object. Call this after running the query and during error handling.

You then include items 2 and 3 in the select list for your query, and item 2 must also be wrapped in an existing no-effect aggregate function like MAX() or MIN(). You can also use this technique for cumulative functions to do things like row numbers.

Some of the comments suggest that the optimizer may try to negate the aggregation effects by optimizing away the calls in some circumstances, though I expect that would be a very rare case indeed. However, I found this question because I took those warnings seriously enough to continue searching for something better.