Export (0) Print
Expand All
This topic has not yet been rated - Rate this topic

Create User-defined Aggregates

You can create a database object inside SQL Server that is programmed in a CLR assembly. Database objects that can leverage the rich programming model provided by the CLR include triggers, stored procedures, functions, aggregate functions, and types.

Like the built-in aggregate functions provided in Transact-SQL, user-defined aggregate functions perform a calculation on a set of values and return a single value.

Creating a user-defined aggregate function in SQL Server involves the following steps:

  • Define the user-defined aggregate function as a class in a Microsoft .NET Framework-supported language. For more information about how to program user-defined aggregates in the CLR, see CLR User-Defined Aggregates. Compile this class to build a CLR assembly using the appropriate language compiler.

  • Register the assembly in SQL Server using the CREATE ASSEMBLY statement. For more information about assemblies in SQL Server, see Assemblies (Database Engine).

  • Create the user-defined aggregate that references the registered assembly using the CREATE AGGREGATE statement.

Note Note

Deploying a SQL Server Project in Microsoft Visual Studio registers an assembly in the database that was specified for the project. Deploying the project also creates a user-defined aggregate in the database for all class definitions annotated with the SqlUserDefinedAggregate attribute. For more information, see Deploying CLR Database Objects.

Note Note

The ability of SQL Server to execute CLR code is off by default. You can create, alter and drop database objects that reference managed code modules, but these references will not execute in SQL Server unless the clr enabled Option is enabled using sp_configure (Transact-SQL).

To create, modify, or drop an assembly

To create a user-defined aggregate

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.