Edit

CLR User-Defined Aggregates - Requirements

  • Article

Applies to: SQL Server

A type in a common language runtime (CLR) assembly can be registered as a user-defined aggregate function, as long as it implements the required aggregation contract. This contract consists of the SqlUserDefinedAggregate attribute and the aggregation contract methods. The aggregation contract includes the mechanism to save the intermediate state of the aggregation, and the mechanism to accumulate new values, which consists of four methods: Init, Accumulate, Merge, and Terminate. When you have met these requirements, you will be able to take full advantage of user-defined aggregates in Microsoft SQL Server. The following sections of this topic provide additional details about how to create and work with user-defined aggregates. For an example, see Invoking CLR User-Defined Aggregate Functions.

SqlUserDefinedAggregate

For more information, see SqlUserDefinedAggregateAttribute.

Aggregation Methods

The class registered as a user-defined aggregate should support the following instance methods. These are the methods that the query processor uses to compute the aggregation:

Method Syntax Description
Init public void Init(); The query processor uses this method to initialize the computation of the aggregation. This method is invoked once for each group that the query processor is aggregating. The query processor may choose to reuse the same instance of the aggregate class for computing aggregates of multiple groups. The Init method should perform any clean-up as necessary from previous uses of this instance, and enable it to re-start a new aggregate computation.
Accumulate public void Accumulate ( input-type value[, input-type value, ...]); One or more parameters representing the parameters of the function. input_type should be the managed SQL Server data type equivalent to the native SQL Server data type specified by input_sqltype in the CREATE AGGREGATE statement. For more information, see Mapping CLR Parameter Data.

For user-defined types (UDTs), the input-type is the same as the UDT type. The query processor uses this method to accumulate the aggregate values. This is invoked once for each value in the group that is being aggregated. The query processor always calls this only after calling the Init method on the given instance of the aggregate-class. The implementation of this method should update the state of the instance to reflect the accumulation of the argument value being passed in.
Merge public void Merge( udagg_class value); This method can be used to merge another instance of this aggregate class with the current instance. The query processor uses this method to merge multiple partial computations of an aggregation.
Terminate public return_type Terminate(); This method completes the aggregate computation and returns the result of the aggregation. The return_type should be a managed SQL Server data type that is the managed equivalent of return_sqltype specified in the CREATE AGGREGATE statement. The return_type can also be a user-defined type.

See Also

CLR User-Defined Types
Invoking CLR User-Defined Aggregate Functions