Skip to content

Latest commit

 

History

History
113 lines (83 loc) · 6.71 KB

create-aggregate-transact-sql.md

File metadata and controls

113 lines (83 loc) · 6.71 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs
CREATE AGGREGATE (Transact-SQL)
CREATE AGGREGATE (Transact-SQL)
markingmyname
maghan
03/14/2017
sql
t-sql
reference
CREATE_AGGREGATE_TSQL
CREATE AGGREGATE
AGGREGATE_TSQL
AGGREGATE
CREATE AGGREGATE statement
aggregate functions [SQL Server], user-defined
user-defined functions [CLR integration]
TSQL

CREATE AGGREGATE (Transact-SQL)

[!INCLUDE SQL Server]

Creates a user-defined aggregate function whose implementation is defined in a class of an assembly in the [!INCLUDEdnprdnshort]. For the [!INCLUDEssDE] to bind the aggregate function to its implementation, the [!INCLUDEdnprdnshort] assembly that contains the implementation must first be uploaded into an instance of [!INCLUDEssNoVersion] by using a CREATE ASSEMBLY statement.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

  
CREATE AGGREGATE [ schema_name . ] aggregate_name  
        (@param_name <input_sqltype>   
        [ ,...n ] )  
RETURNS <return_sqltype>  
EXTERNAL NAME assembly_name [ .class_name ]  
  
<input_sqltype> ::=  
        system_scalar_type | { [ udt_schema_name. ] udt_type_name }  
  
<return_sqltype> ::=  
        system_scalar_type | { [ udt_schema_name. ] udt_type_name }

[!INCLUDEsql-server-tsql-previous-offline-documentation]

Arguments

schema_name
Is the name of the schema to which the user-defined aggregate function belongs.

aggregate_name
Is the name of the aggregate function you want to create.

@ param_name
One or more parameters in the user-defined aggregate. The value of a parameter must be supplied by the user when the aggregate function is executed. Specify a parameter name by using an "at" sign (@) as the first character. The parameter name must comply with the rules for identifiers. Parameters are local to the function.

system_scalar_type
Is any one of the [!INCLUDEssNoVersion] system scalar data types to hold the value of the input parameter or return value. All scalar data types can be used as a parameter for a user-defined aggregate, except text, ntext, and image. Nonscalar types, such as cursor and table, cannot be specified.

udt_schema_name
Is the name of the schema to which the CLR user-defined type belongs. If not specified, the [!INCLUDEssDE] references udt_type_name in the following order:

  • The native SQL type namespace.

  • The default schema of the current user in the current database.

  • The dbo schema in the current database.

udt_type_name
Is the name of a CLR user-defined type already created in the current database. If udt_schema_name is not specified, [!INCLUDEssNoVersion] assumes the type belongs to the schema of the current user.

assembly_name [ .class_name ]
Specifies the assembly to bind with the user-defined aggregate function and, optionally, the name of the schema to which the assembly belongs and the name of the class in the assembly that implements the user-defined aggregate. The assembly must already have been created in the database by using a CREATE ASSEMBLY statement. class_name must be a valid [!INCLUDEssNoVersion] identifier and match the name of a class that exists in the assembly. class_name may be a namespace-qualified name if the programming language used to write the class uses namespaces, such as C#. If class_name is not specified, [!INCLUDEssNoVersion] assumes it is the same as aggregate_name.

Remarks

By default, the ability of [!INCLUDEssNoVersion] to run CLR code is off. You can create, modify, and drop database objects that reference managed code modules, but the code in these modules will not run in an instance of [!INCLUDEssNoVersion] unless the clr enabled option is enabled by using sp_configure.

The class of the assembly referenced in assembly_name and its methods, should satisfy all the requirements for implementing a user-defined aggregate function in an instance of [!INCLUDEssNoVersion]. For more information, see CLR User-Defined Aggregates.

Permissions

Requires CREATE AGGREGATE permission and also REFERENCES permission on the assembly that is specified in the EXTERNAL NAME clause.

Examples

The following example assumes that a StringUtilities.csproj sample application is compiled. For more information, see String Utility Functions Sample.

The example creates aggregate Concatenate. Before the aggregate is created, the assembly StringUtilities.dll is registered in the local database.

USE AdventureWorks2022;  
GO  
DECLARE @SamplesPath nvarchar(1024)  
-- You may have to modify the value of the this variable if you have  
--installed the sample some location other than the default location.  
  
SELECT @SamplesPath = REPLACE(physical_name, 'Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\master.mdf', 'Microsoft SQL Server\130\Samples\Engine\Programmability\CLR\')   
     FROM master.sys.database_files   
     WHERE name = 'master';  
  
CREATE ASSEMBLY StringUtilities FROM @SamplesPath + 'StringUtilities\CS\StringUtilities\bin\debug\StringUtilities.dll'  
WITH PERMISSION_SET=SAFE;  
GO  
  
CREATE AGGREGATE Concatenate(@input nvarchar(4000))  
RETURNS nvarchar(4000)  
EXTERNAL NAME [StringUtilities].[Microsoft.Samples.SqlServer.Concatenate];  
GO

See Also

DROP AGGREGATE (Transact-SQL)