Show TOC

CLR Aggregate Functions (SQL Server)Locate this document in the navigation structure

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 Start of the navigation path Model Next navigation step Aggregates End of the navigation path 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 Start of the navigation path New Next navigation step Aggregate End of the navigation path.

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.