5.4. User-Defined Aggregate Functions A user-defined aggregate (UDA ) function returns a scalar result that is the result of a calculation on values in a set of rows. Examples of such functions include built-in SQL Server aggregate functions such as SUM, AVG, MIN, and MAX. A CLR UDA function is implemented as a structure or class in a .NET Framework assembly. A CLR UDA function can be invoked in T-SQL statements with the same rules that apply to system aggregate functions. To implement a CLR UDA function, you have to write only the code that implements the accumulation logiciteration over the result set and computing accumulated values are managed by the query processor. Specifically, you must implement an aggregation contract that defines mechanisms to save the intermediate state of the aggregation and to accumulate new values. This aggregation contract consists of four methods:
public void Init( ) Invoked once for each group that the query processor is aggregating to initialize the aggregate computation. This method should clean up previous uses of the instance, because the query processor can choose to reuse an instance of an aggregate class to compute aggregates for multiple groups.
public void Accumulate(input_type value) The query processor invokes this method to accumulate aggregate values. The method is invoked for each value in the group being accumulated. The input_type argument is the managed SQL Server data type equivalent to the native SQL Server data type specified by the argument.
public void Merge(udagg_class value) Used to merge a second instance of this aggregate class with the current instance. The query processor can invoke this method to merge partial computations of an aggregate on group partitions.
public return_type Terminate( ) Completes the aggregation and returns the result. The return_type is a managed SQL Server data type equivalent to the return_sqltype specified in the CREATE AGGREGATE T-SQL statement used to create the CLR aggregate function. You identify a UDA function by annotating the implementing class with the SqlUserDefinedAggregate attribute, which indicates that a class should be registered as a UDA function. The SqlUserDefinedAggregate attribute has the following syntax: SqlUserDefinedAggregate [ (aggregate-attribute [,...] ) ] aggregate-attribute::= Format = {Native | UserDefined} IsInvariantToDuplicates = {true | false} IsInvariantToNulls = {true | false} IsInvariantToOrder = {true | false} IsNullIfEmpty = {true | false} | MaxByteSize = n
where:
Format = {Native | UserDefined} Specifies the serialization format for the typeeither Native or UserDefined. Native serialization uses a simple algorithm to efficiently serialize the type. Native serialization is recommended for simple types containing only fields of the following types: bool, byte, sbyte, short, ushort, int, uint, long, ulong, float, double, SqlByte, SqlInt16, SqlInt32, SqlInt64, SqlDateTime, SqlSingle, SqlDouble, SqlMoney, and SqlBoolean. Native serialization can also contain UDTs that use Native serialization. Native serialization has the following requirements: All the fields of the type must be blittabledata types that have a common representation in both managed and unmanaged memory and therefore do not need to be converted when passed between managed and unmanaged code. The following types from the System namespace are blittable: Byte, SByte, UInt16, Int32, UInt32, Int64, IntPtr, and UIntPtr. One-dimensional arrays of blittable types and formatted value types containing only blittable types are also blittable. The type must not specify the MaxByteSize property. The type must not have any fields that are not serialized.
UserDefined serialization controls the serialization through code and has the following requirements: You must specify the MaxByteSize property of the SqlUserDefinedAggregate attribute. The class or structure implementing the type must implement the Read( ) and Write( ) methods of the IBinarySerializable interface to read and write the byte stream.
IsInvariantToDuplicates Specifies whether the aggregate is invariant to duplicates. For example, MAX and MIN are invariant to duplicates, and AVG and SUM are not.
IsInvariantToNulls Specifies whether the aggregate is invariant to nulls. For example, MAX and MIN are invariant to nulls, and COUNT is not (since nulls are included in the count).
IsInvariantToOrder Specifies whether the aggregate is invariant to the order of the values. Specifying true gives the query optimizer more flexibility in choosing an execution plan and can result in improved performance.
IsNullIfEmpty Specifies whether the aggregate returns a null reference if no values are accumulated. Otherwise the value that the initialized value of the variable returned by the Terminate( ) method is returned.
MaxByteSize The maximum size of the UDT instance. MaxByteSize must be specified if the Format property is set to UserDefined. The following example creates, registers, and executes a UDA function that returns the sum of a SqlMoney column in a table. Follow these steps: Using the Visual Studio 2005 IDE, create a new SQL Server project named Uda. Create an aggregate item in the project. Name the item SumMoney.cs. Empty code blocks are created for the four required methods. Replace the code in SumMoney.cs with the following code: using System; using System.Data; using System.Data.Sql; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; [Serializable] [SqlUserDefinedAggregate(Format.Native)] public struct SumMoney { private SqlMoney sum; public void Init( ) { sum = 0; } public void Accumulate(SqlMoney Value) { sum += Value; } public void Merge(SumMoney Group) { sum += Group.sum; } public SqlMoney Terminate( ) { return sum; } }
Build the solution. In SQL Server Management Studio, register the assembly and create the UDA function by executing the following statement: USE AdventureWorks GO CREATE ASSEMBLY SumMoney FROM 'C:\PSS2005\Uda\Uda\bin\Debug\Uda.dll' GO CREATE AGGREGATE SumMoneyUda ( @Value money ) RETURNS money EXTERNAL NAME SumMoney.SumMoney
Execute the aggregate function on the Sales.SalesOrderHeader table in Aventure-Works: SELECT dbo.SumMoneyUda(SubTotal), dbo.SumMoneyUda(TaxAmt), dbo.SumMoneyUda(Freight), dbo.SumMoneyUda(TotalDue) FROM Sales.SalesOrderHeader
The results shown in Figure 5-7 are returned (which are the same as the totals returned by the built-in SUM function).
Figure 5-7. Results for UDA function example 
|
