Working with User-Defined Aggregates

Working with User-Defined Aggregates

SQL Server has aggregate functions for such things as MAX, MIN, COUNT, and SUM, but what if you want to create a new aggregate function? You can create user-defined aggregates using managed code, which means you can create a function that will be executed once for every row in your rowset, using the column of your choice.

To create a user-defined aggregate in Visual Studio .NET, simply add an Aggregate template to your project. This template consists of a structure (C# struct), not a partial class. The structure has the SqlUserDefinedAggregate attribute, which tells Visual Studio .NET how to deploy the structure. The user-defined aggregate must implement the following four methods.

• Init Executes when the aggregate is initialized. You must initialize your fields in this method because SQL Server might choose to reuse your aggregate instead of creating a new one, and this method will be called with the expectation that the aggregate will be "like new" and ready for use.

• Accumulate Called once for each row that is to be aggregated.

• Merge If SQL Server decides to use multiple threads to perform the aggregation, each thread uses its own aggregate object. The accumulated results of the objects are merged to get the combined result. Notice that Merge is handed an aggregate object, not a value, which makes it possible to access the object's member variables.

• Terminate Used to return the results.

The Format property on the SqlUserDefinedAggregate attribute tells SQL Server how to handle serialization. If all members of the class are value types, you can use Format.Native. To serialize more complex data, you must do your own serialization.

You should be careful to always test the value parameter (that is passed to the Accumulate method) for null values because SQL data types are nullable. There is also an optimizer hint that is called IsInvariantToNulls, which is a property of the SqlUserDefinedAggregate attribute that you can set to true when you don't need the Accumulate to be called with null values. In many cases, you will want to ignore nulls, but if you are counting the total number of items, you might want to use null as part of the count. For example, the COUNT aggregate that SQL Server has does not count nulls if you are performing a count on a field, but you can create an aggregate that does count the nulls.

You can also choose the data type to pass into the Accumulate method, and the type to be returned from the Terminate method.

The SqlUserDefinedAggregate attribute also contains a property called IsNullIfEmpty. If you set IsNullIfEmpty to true, the aggregate returns null when there are no values to aggregate.

An example of an aggregate is one that gets the minimum date/time and the maximum date/ time and returns the time span, as shown in the following code.

Visual Basic

Imports System Imports System.Data Imports System.Data.Sql Imports System.Data.SqlTypes Imports Microsoft.SqlServer.Server <Serializable()> _ <Microsoft.SqlServer.Server.SqlUserDefinedAggregate( _    Format.Native, IsNullIfEmpty:=True, IsInvariantToNulls:=True)> _ Public Structure DateTimeSpanVb    Private minDate As SqlDateTime    Private maxDate As SqlDateTime    Public Sub Init()       minDate = SqlDateTime.Null       maxDate = SqlDateTime.Null    End Sub    Public Sub Accumulate(ByVal value As SqlDateTime)       If value.IsNull Then Return       If ((minDate.IsNull) Or (value.CompareTo(minDate) < 0)) Then          minDate = value       End If       If ((maxDate.IsNull) Or (value.CompareTo(maxDate) > 0)) Then          maxDate = value       End If    End Sub    Public Sub Merge(ByVal Group As DateTimeSpanVb)       Accumulate(Group.minDate)       Accumulate(Group.maxDate)    End Sub    Public Function Terminate() As SqlString       If (maxDate.IsNull Or minDate.IsNull) Then          Return SqlString.Null       End If       Dim ts As TimeSpan       ts = maxDate.Value - minDate.Value       Return ts.ToString()    End Function End Structure