CLR aggregate functions

Chapter 15 DBMS-Specific Features

CLR aggregate functions

An aggregate function performs a calculation on a set of values and returns a single value. Traditionally, Microsoft SQL Server has supported only built-in aggregate functions, such as SUM or MAX, that operate on a set of input scalar values and generate a single aggregate value from that set. SQL Server integration with the Microsoft .NET Framework common language runtime (CLR) now allows developers to create custom aggregate functions in managed code, and to make these functions accessible to Transact-SQL or other managed code. PowerDesigner models aggregate functions as extended objects with a stereotype of <<Aggregate>>.

Creating an aggregate function

You can create an aggregate function in any of the following ways:

Select Model→Aggregate Objects to access the List of Aggregate Objects, and click the Add a Row tool
Right-click the model or package in the Browser, and select New→Aggregate

Aggregate function properties

You can modify an object's properties from its property sheet. To open an aggregate function property sheet, double-click its diagram symbol or its Browser entry in the Aggregate Objects folder.

The following extended attributes are available on the Microsoft tab:

Name	Scripting Name	Description
Owner	Owner	Specifies the name of a schema as the owner of the aggregate function.
Assembly	Assembly	Specifies the assembly to bind with the aggregate function.
Class name	Class	Specifies the name of the class in the assembly that implements the aggregate function. If the class name is not specified, SQL Server assumes it is the same as the aggregate name.
Parameter name	InputParameterName	Specifies the name of the input parameter.
Type	InputParameterType	Specifies the type of the input parameter. All scalar data types or CLR user-defined types can be used, except text, ntext, and image.
Return type	ReturnType	Specifies the return type of the aggregate function. All scalar data types or CLR user-defined types can be used as return type, except text, ntext, and image.