Requirements for CLR User-Defined Aggregates
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 2005. 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
Every user-defined aggregate must be annotated with the SqlUserDefinedAggregate custom attribute. This attribute indicates to SQL Server that the type conforms to the contract for a user-defined aggregate. There are two required properties on the SqlUserDefinedAggregate attribute that control the serialization format used. The following table describes these properties.
| Property | Description |
|---|---|
Format |
The serialization format for this type. The serialization format may be one of native or user-defined. Each serialization format has its own set of requirements. |
MaxByteSize |
The maximum size in bytes needed to store the state for this aggregate during computation. The maximum allowed value is 8000. For an aggregate with user-defined serialization specified, MaxByteSize refers to the total size of the serialized data. Consider an aggregate serializing a string of 10 characters (System.Char). When the string is serialized using a BinaryWriter, the total size of the serialized string is 22 bytes: 2 bytes per Unicode UTF-16 character, multiplied by the maximum number of characters, plus 2 control bytes of overhead incurred from serializing a binary stream. So, when determining the value of MaxByteSize, the total size of the serialized data must be considered: the size of the data serialized in binary form plus the overhead incurred by serialization. |
The SqlUserDefinedAggregate attribute is similar in some ways to the attribute SqlUserDefinedType attribute. It also takes a format for the serialization of the type and specifies a maximum size. The following table summarizes some additional properties for this attribute.
IsInvariantToDuplicates |
Optional property. Returns true only if the aggregate is invariant to duplicates. For example, MAX and MIN functions would satisfy this condition, whereas SUM would not. |
IsInvariantToNulls |
Optional property. Specifies whether the aggregate is invariant to NULLs. For example, MIN and SUM satisfy this condition, whereas COUNT does not. |
IsInvariantToOrder |
Reserved for future use. This property is not currently used by the query processor: order is currently not guarenteed. |
IsNullIfEmpty |
Property used by the query processor. Indicates if the aggregate returns null if no values have been accumulated. |
The query processor uses serialization when it needs to propagate temporary results of an aggregation into work-tables. For more information about the SqlUserDefinedAggregate attribute, see "SqlUserDefinedAggregateAttribute Class" in the .NET Framework SDK.
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:
public void Init(); /* needed for empty group */
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.
public void Accumulate ( input-type value);
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 SQL Server Data Types and Their .NET Framework Equivalents.
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.
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.
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
Concepts
CLR User-Defined Types
Invoking CLR User-Defined Aggregate Functions