CLR Aggregate Functions (SQL Server)

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 > Aggregates to access the List of Aggregates, and click the Add a Row tool.
Right-click the model (or a 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 Aggregates folder.

The following extended attributes are available on the Microsoft tab:

Name	Description
Schema	Specifies the name of a schema as the owner of the aggregate function. Scripting name: Owner
Assembly	Specifies the assembly to bind with the aggregate function. Scripting name: Assembly
Class name	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. Scripting name: Class
Parameter name	[v2005] Specifies the name of the input parameter. Scripting name: InputParameterName
Type	[v2005] Specifies the type of the input parameter. All scalar data types or CLR user-defined types can be used, except text, ntext, and image. Scripting name: InputParameterType
Return type	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. Scripting name: ReturnType
Length	Specifies the length of return data type. Scripting name: ReturnTypeLength
Precision	Specifies the precision of return data type. Scripting name: ReturnTypePrec

For v2008 and higher, the Parameters tab allows you to list the name, type, length, and precision of any parameters.