CREATE AGGREGATE (Transact-SQL)
Creates a user-defined aggregate function whose implementation is defined in a class of an assembly in the Microsoft .NET Framework. For the SQL Server 2005 Database Engine to bind the aggregate function to its implementation, the .NET Framework assembly that contains the implementation must first be uploaded into an instance of SQL Server by using a CREATE ASSEMBLY statement.
Note
By default, the ability of SQL Server 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 SQL Server unless the clr enabled option is enabled by using sp_configure.
.gif "Topic link icon")
Transact-SQL Syntax Conventions
Syntax
CREATE AGGREGATE [ schema_name . ] aggregate_name
(@param_name <input_sqltype> )
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 }
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
Is a parameter in the user-defined aggregate. The value of the 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 SQL Server 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 Database Engine 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, SQL Server 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 SQL Server 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, SQL Server assumes it is the same as aggregate_name.
Remarks
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 SQL Server. 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 the SQL Server Database Engine samples are installed in the default location of the local computer and the StringUtilities.csproj sample application is compiled. For more information, see String Utilities Sample.
The example creates aggregate Concatenate. Before the aggregate is created, the assembly StringUtilities.dll is registered in the local database.
USE AdventureWorks;
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\MSSQL.1\MSSQL\DATA\master.mdf', 'Microsoft SQL Server\90\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