Skip to content

create-user-defined-aggregates.md

Latest commit

 

History

History
52 lines (37 loc) · 3.89 KB

create-user-defined-aggregates.md

File metadata and controls

52 lines (37 loc) · 3.89 KB
title Create User-defined Aggregates | Microsoft Docs
ms.custom
ms.date 03/16/2017
ms.prod sql
ms.prod_service database-engine
ms.reviewer
ms.technology
ms.topic conceptual
helpviewer_keywords
aggregate functions [SQL Server], user-defined
user-defined functions [CLR integration]
ms.assetid c278b746-6323-4b32-b460-239915acc067
author rothja
ms.author jroth

Create User-defined Aggregates

[!INCLUDEtsql-appliesto-ss2016-xxxx-xxxx-xxx-md] You can create a database object inside [!INCLUDEssNoVersion] 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 [!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.

Note

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 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] The ability of [!INCLUDEssNoVersion] 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 [!INCLUDEssNoVersion] 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

See Also

Common Language Runtime (CLR) Integration Programming Concepts