Professional SQL Server™ 2005 Programming

14.7. Creating Aggregate Functions

Now this one is going to be the one thing in this chapter that's really new. When we look at user-defined data types a little later, we'll see something with a bigger shift than some of the other constructs we've looked at here, but aggregate functions are something that you can't do any other way — the T-SQL version of a UDF does not allow for aggregation.

So, what am I talking about here? Well, examples are SUM, AVG, MIN, and MAX. These all look over a set of data and then return a value that is based on some analysis of the whole. It may be based on your entire result set or on some criteria defined in the GROUP BY clause.

Performing the analysis required to support your aggregate gets rather tricky. Unlike other functions, where everything can be contained in a single call to your procedure, aggregates require mixing activities your function does (the actual aggregation part) with activities SQL Server isdoing at essentially the same time (organizing the groups for the GROUP BY for example). The result is something of staged calls to your assembly class. Your class can be called at any of four times and can support methods for each of these calls:

• Init — This supports the initialization of your function. Since you're aggregating, there's a good chance that you are setting some sort of accumulator or other holding value — this is the method where you would initialize variables that support the accumulation or holding value.

• Accumulate — This ...