Overview

• Managed database objects, such as stored procedures or triggers, are compiled and then deployed in units called an assembly.
• Managed DLL assemblies must be registered in Microsoft SQL Server before the functionality the assembly provides can be used.
• To register an assembly in a SQL Server database, use the CREATE ASSEMBLY statement.

CREATE ASSEMBLY

• CREATE ASSEMBLY creates a managed application module that contains class metadata and managed code as an object in an instance of SQL Server.
• By referencing this module, common language runtime (CLR) functions, stored procedures, triggers, user-defined aggregates, and user-defined types can be created in the database.
• SQL aggregate functions return a single value, calculated from values in a column.
• You can create a database object inside SQL Server 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 Transact-SQL, user-defined aggregate functions perform a calculation on a set of values and return a single value.

CREATE AGGREGATE

• CREATE AGGREGATE creates a user-defined aggregate function whose implementation is defined in a class of an assembly in the .NET Framework.
• For the 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.
• 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.

DECLARE @SamplesPath NVARCHAR(1024);
-- You may have to modify the value of this variable if you have
-- installed the sample some location other than the default location.
SELECT @SamplesPath = REPLACE(physical_name,
	'Microsoft SQL Server\MSSQL11.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