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 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.

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.

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

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 Readme_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\MSSQL10.MSSQLSERVER\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

Reference

Help and Information