| title | description | author | ms.author | ms.date | ms.service | ms.topic | helpviewer_keywords | ||
|---|---|---|---|---|---|---|---|---|---|
Create User-defined Aggregates |
Create User-defined Aggregates |
rwestMSFT |
randolphwest |
06/28/2022 |
sql |
conceptual |
|
[!INCLUDE sqlserver2016]
You can create a database object inside [!INCLUDEssNoVersion] that is programmed in a CLR assembly. Database objects that can use 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 [!INCLUDEtsql], user-defined aggregate functions perform a calculation on a set of values and return a single value.
Creating a user-defined aggregate function in [!INCLUDEssNoVersion] involves the following steps:
-
Define the user-defined aggregate function as a class in a [!INCLUDEmsCoName] .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 [!INCLUDEssNoVersion] using the CREATE ASSEMBLY statement. For more information about assemblies in [!INCLUDEssNoVersion], see Assemblies (Database Engine).
-
Create the user-defined aggregate that references the registered assembly using the CREATE AGGREGATE statement.
Executing CLR code is off by default in [!INCLUDEssNoVersion]. You can create, alter, and drop database objects that reference managed code modules, but these references won't execute in [!INCLUDEssNoVersion] unless the clr enabled option is enabled by using sp_configure (Transact-SQL).
Deploying a SQL Server Project in [!INCLUDEmsCoName] [!INCLUDEvsprvs] registers an assembly in the database that was specified for the project. Deploying the project also creates CLR functions in the database for all methods annotated with the SqlFunction attribute. For more information, see Deploying CLR Database Objects.