Section 4.6. DDL Support for CLR Integration

4.6. DDL Support for CLR Integration

SQL Server introduces new T-SQL statements to create and manage .NET assemblies and UDTs, and enhances other T-SQL statements to create and manage functions, stored procedures, triggers, and UDA functions created from CLR assemblies. These statements are described in Table 4-4.

Table 4-4. New and changed T-SQL statements to support CLR integration

Scope	DDL statement	New T-SQL statement	Description
.NET Framework assembly	CREATE ASSEMBLY	Yes	Loads assembly into SQL Server.
	ALTER ASSEMBLY	Yes	Changes a loaded assembly.
	DROP ASSEMBLY	Yes	Unloads an assembly from SQL Server.
User-defined aggregate function	CREATE AGGREGATE	Yes	Creates a UDA function in a SQL Server database from a UDA function implemented as a class in a .NET Framework assembly. The assembly containing the class must first be registered in SQL Server with the CREATE ASSEMBLY T-SQL statement.
	DROP AGGREGATE	Yes	Removes a UDA function from a SQL Server database.
User-defined type	CREATE TYPE	No	Creates a UDT in a SQL Server database from a type implemented as a class or structure in a .NET Framework assembly. The assembly containing the class or structure must first be registered in SQL Server with the CREATE ASSEMBLY T-SQL statement.
	DROP TYPE	No	Removes a UDT from a SQL Server database.
Stored procedure	CREATE PROCEDURE	No	Creates a stored procedure in a SQL Server database from a CLR stored procedure implemented as a method in a .NET Framework assembly. The assembly containing the method must first be registered in SQL Server with the CREATE ASSEMBLY T-SQL statement.
	ALTER PROCEDURE	No	Changes a stored procedure previously created with the CREATE PROCEDURE T-SQL statement.
	DROP PROCEDURE	No	Removes a stored procedure from a SQL Server database.
User-defined function (scalar-valued or table-valued)	CREATE FUNCTION	No	Creates a UDF in a SQL Server database from a CLR UDF implemented as a method in a .NET Framework assembly. The assembly containing the method must first be registered in SQL Server with the CREATE ASSEMBLY T-SQL statement.
	ALTER FUNCTION	No	Changes a UDF previously created with the CREATE FUNCTION T-SQL statement.
	DROP FUNCTION	No	Removes a UDF from a SQL Server database.
Trigger	CREATE TRIGGER	No	Creates a DML or DDL trigger in a SQL Server database from a CLR trigger implemented as a method in a .NET Framework assembly. The assembly containing the method must first be registered in SQL Server with the CREATE ASSEMBLY T-SQL statement.
	ALTER TRIGGER	No	Changes a trigger previously created with the CREATE TRIGGER T-SQL statement.
	DROP TRIGGER	No	Removes a trigger from a SQL Server database.

The statements are described in detail in the following subsections. Chapter 5 presents in-depth discussions and examples of creating these objects in C#.

4.6.1. CLR Routine Metadata

Catalog views return metadata used by the SQL Server 2005 Database Engine. The sys.all_objects catalog view returns a row for each user-defined object and system object in the current database. The type column specifies the object typethe CLR routine type values are shown in Table 4-5.

Table 4-5. CLR object type values

sys.all_objects type column value	CLR object type
AF	Aggregate function
FS	Scalar-valued function
FT	Table-valued function
PC	Stored procedure
TA	Trigger

For example, the following T-SQL statement returns information about all CLR stored procedures in the AdventureWorks database:

     USE AdventureWorks     GO     SELECT * FROM sys.all_objects     WHERE type='PC' 

The sys.all_objects catalog view does not return information for DDL triggers. Use the sys.triggers catalog view instead to return information for all DML and DDL triggers in the current database.

The sys.all_objects catalog view does not return information for UDTs. The sys.types catalog view returns information for all system and user-defined types in the current database. The sys.assembly_types catalog view returns information for all CLR UDTs.

SQL Server 2005 provides catalog views that contain information about registered assemblies and CLR functions, stored procedures, triggers, UDTs, and UDA functions defined from registered assemblies. These catalog views are described in Table 4-6 and detailed in the following subsections.

Table 4-6. Catalog views returning metadata about registered .NET Framework assemblies and CLR routines

Catalog view	Description
sys.assemblies	A row for each assembly registered in the current database
sys.assembly_files	A row for each file that makes up an assembly
sys.assembly_modules	A row for each function, CLR stored procedure, or trigger
sys.assembly_references	A row for each pair of assemblies directly referencing each other
sys.assembly_types	A row for each CLR UDT

4.6.1.1. sys.assemblies

The sys.assemblies catalog view contains a row for each assembly registered in the current database. Table 4-7 describes the columns in this view.

Table 4-7. sys.assemblies catalog view columns

Column name	Description
name	The name of the assembly, unique within the schema.
principal_id	The ID of the principal that owns the schema.
assembly_id	The assembly ID number, unique within a database.
permission_set	The code access permissions for the assembly; one of the following numeric values: 1 = Safe Access 2 = External Access 3 = Unsafe Access
permission_set_desc	A description of code access permissions specified by the value of the permission_set column; one of the following string values: SAFE_ACCESS, EXTERNAL_ACCESS, or UNSAFE_ACCESS.
is_visible	A numeric value indicating the visibility of the assembly: 0 = The assembly can be called only by other assemblies. 1 = The assembly can be used to create CLR UDFs, stored procedures, triggers, UDTs, and UDA functions.
clr_name	Canonical string that uniquely identifies the assembly. The string encodes the simple name, version number (version), culture (culture), public key (publickeytoken), and architecture (processorarchitecture) of the assembly.
create_date	The date that the assembly was created or registered.

The following T-SQL statement returns a result set of all the CLR assemblies registered in the current database:

     SELECT * FROM sys.assemblies; 

4.6.1.2. sys.assembly_files

The sys.assembly_files catalog view contains a row for each file in each registered assembly in the current database. Table 4-8 describes the columns in this view.

Table 4-8. sys.assembly_files catalog view columns

Column name	Description
assembly_id	The ID of the assembly to which the file belongs.
name	The name of the assembly file.
file_id	The ID of the file, unique within an assembly. The root assembly has a file ID of 1. Files added to the assembly have a file ID of 2 or greater.
content	The binary contents of the file.

4.6.1.3. sys.assembly_modules

The sys.assembly_modules catalog view contains a row for each CLR function (scalar-valued, table-valued, and aggregate), stored procedure, or trigger defined in a .NET Framework assembly in the current database. Table 4-9 describes the columns in this view.

Table 4-9. sys.assembly_modules catalog view columns

Column name	Description
object_id	The ID of the module (CLR routine), unique within the database.
assembly_id	The ID of the assembly from which the module was created.
assembly_class	The name of the class within the assembly that defines the module.
assembly_method	The name of the method within the assembly_class that defines this module. This value is NULL for aggregate functions.
null_on_null_input	Indicates whether the module returns NULL if any arguments are NULL.
execute_as_principal_id	The database principal ID for the execution context. If this value is NULL, the execution context is CALLER.

4.6.1.4. sys.assembly_references

The sys.assembly_references catalog view contains a row for each pair of assemblies registered in the current database where one assembly directly references another. Table 4-10 describes the columns in this view.

Table 4-10. sys.assembly_references catalog view columns

Column name	Description
assembly_id	The ID of the assembly that has a reference to another assembly
referenced_assembly_id	The ID of the assembly being referenced

4.6.1.5. sys.assembly_types

The sys.assembly_types catalog view contains a row for each UDT in the current database that is defined in a CLR assembly. Table 4-11 describes the columns in this view.

Table 4-11. sys.assembly_types catalog view columns

Column name	Description
<inherited_columns>	Columns inherited from sys.types catalog view
assembly_id	The ID of the assembly from which the UDT was created
assembly_class	The name of the class within the assembly that defines the UDT
is_binary_ordered	Indicates whether sorting the bytes of the type is equivalent to sorting the type using comparison operators
is_fixed_length	Indicates whether the length of the type is the same as the maximum length (max_length in sys.types)
prog_id	The ProgID of the type exposed to COM
assembly_qualified_name	The assembly qualified type name

4.6.2. Assembly Management

A .NET Framework assembly contains classes and methods that can implement CLR routines in SQL Server 2005. You first have to register the assembly with SQL Server by using the CREATE ASSEMBLY T-SQL statement as you did earlier in the "Hello World Example" section. A registered assembly can be modified using the ALTER ASSEMBLY statement, or removed from the server using the DROP ASSEMBLY statement. These three new T-SQL statements are described in the following subsections.

4.6.2.1. CREATE ASSEMBLY

The CREATE ASSEMBLY T-SQL statement registers a .NET Framework assembly as an object within SQL Server from which CLR stored procedures, UDFs, triggers, UDA functions, and UDTs can be created.

The CREATE ASSEMBLY syntax is:

     CREATE ASSEMBLY assembly_name       [ AUTHORIZATION owner_name ]       FROM { client_assembly_specifier | assembly_bits [,...n] }       [ WITH PERMISSION_SET = { SAFE | EXTERNAL_ACCESS | UNSAFE } ]     client_assembly_specifier :: =       '[\\computer_name\]share_name\[path\]manifest_file_name'       | '[local_path\]manifest_file_name'     assembly_bits :: =       { varbinary_literal | varbinary_expression } 

where:

assembly_name

Specifies the name of the assembly, which must be unique within the database.

AUTHORIZATION owner_name

Specifies the name of the user or role that is the owner of the assembly. If not specified, ownership is assigned to the current user.

FROM

Specifies the .NET Framework assembly to load.

client_assembly_specifier

Specifies the local path or Universal Naming Convention (UNC) network location where the assembly is located and the manifest filename for the assembly. Multimodule assemblies are not supported. Dependent assemblies are either automatically uploaded from the same location or loaded from the current database if owned by the same principalCREATE ASSEMBLY fails if either is not possible.

assembly_bits

Specifies a list of binary values that make up the assembly and its dependent assemblies. The root-level assembly must be specified first followed by the dependent assemblies in any order.

PERMISSION_SET = { SAFE | EXTERNAL_ACCESS | UNSAFE }

Specifies the code-access security when SQL Server accesses the assembly. If not specified, the default is SAFE.

Multiple versions of the same assembly can be uploaded to the server. These assemblies must have different version numbers or cultures and must be registered using unique assembly names within SQL Server.

4.6.2.2. ALTER ASSEMBLY

The ALTER ASSEMBLY T-SQL statement modifies the properties of an assembly previously registered using the CREATE ASSEMBLY statement and refreshes the assembly with the latest version.

The ALTER ASSEMBLY syntax is:

     ALTER ASSEMBLY assembly_name       [ FROM { client_assembly_specifier | assembly_bits [ ,...n ] } ]       [ WITH assembly_option [ ,...n ] ]       [ DROP FILE { file_name [ ,...n ] | ALL } ]       [ ADD FILE FROM       {         client_file_specifier [ AS file_name ]         | file_bits AS file_name       } [,...n ] ]     client_assembly_specifier :: =       '\\computer_name\share-name\[path\]manifest_file_name'       | '[local_path\]manifest_file_name'     assembly_bits :: =       { varbinary_literal | varbinary_expression }     assembly_option :: =       PERMISSION_SET { SAFE | EXTERNAL_ACCESS | UNSAFE }       | VISIBILITY { ON | OFF } ]       | UNCHECKED DATA 

where:

assembly_name

Specifies the name of the registered assembly to alter.

FROM

Specifies the .NET Framework assembly to refresh with the latest copy.

DROP FILE { file_name [ ,...n ] | ALL }

Removes the file associated with the assembly or all files associated with the assembly. DROP FILE executes before ADD FILE if both are specified.

ADD FILE FROM { client_file_specifier [ AS file_name ] | file_bits AS file_name }

Uploads a file that is to be associated with the assembly from the location specified by the client_file_specifier argument or from the binary values that make up the file specified by the file_bits argument. The file_name argument specifies the name to use to store the file in SQL Server. If the file_name argument is not specified with a client_file_specified argument, the filename part of the client_file_specifier is used as the name in SQL Server.

VISIBILITY { ON | OFF }

Specifies whether the assembly can be used to create CLR stored procedures, functions, triggers, UDTs, and UDA functions. Assemblies with VISIBILITY = OFF can be called only by other assemblies . The default VISIBILITY is ON.

UNCHECKED DATA

Alters the assembly even if there are tables with columns or check constraints that reference methods in the assembly or if there are CLR UDTs that are dependent on the assembly and use User-Defined serialization format. Only members of db_owner and db_ddlowner can specify this option.

Other arguments are the same as defined for the CREATE ASSEMBLY statement.

Executing ALTER ASSEMBLY does not affect currently executing sessions running the assembly being alteredthey complete using the unaltered assembly. A new application domain is created running the latest bits for new users of the assembly.

If the FROM clause is not specified, the assembly is refreshed with the latest copy of the assembly rebinding CLR routines to the latest implementation in the assembly.

4.6.2.3. DROP ASSEMBLY

The DROP ASSEMBLY statement removes an assembly previously registered with the CREATE ASSEMBLY statement. The assembly and all of its associated files are removed from the database.

The DROP ASSEMBLY syntax is:

     DROP ASSEMBLY assembly_name     [ WITH NO DEPENDENTS ] 

where:

assembly_name

Specifies the name of the registered assembly to drop

WITH NO DEPENDENTS

Specifies that dependent assemblies are not to be dropped

Executing DROP ASSEMBLY does not affect currently executing sessions running the assembly being droppedthey run to completion. New attempts to invoke code in the assembly fail.

You cannot drop an assembly that is referenced by another assembly or that is used by a CLR function, stored procedure, trigger, UDT, or UDA function.

4.6.3. User-Defined Functions

A user-defined function (UDF ) is a routine written by the user that returns either a scalar value (scalar-valued function) or a table (table-valued function). SQL Server 2005 functions can be created directly from T-SQL statements or from methods in a registered .NET Framework assembly.

UDFs are created, changed, and removed in SQL Server using the CREATE FUNCTION, ALTER FUNCTION, and DROP FUNCTION T-SQL statements. These statements have been enhanced in SQL Server 2005 to support CLR UDF management. The following subsections describe the enhancements.

4.6.3.1. CREATE FUNCTION

The CREATE FUNCTION T-SQL statement has been enhanced in SQL Server 2005 to let you create a CLR scalar-valued or table-valued UDF from a UDF implemented as a method in a .NET Framework assembly. You must first register the assembly using the CREATE ASSEMBLY statement. The CREATE FUNCTION syntax for creating CLR UDFs follows:

     CREATE FUNCTION [ schema_name. ] function_name (       [ { @parameter_name [AS] [ type_schema_name. ] scalar_parameter_data_type }       [ ,...n ] ] )     RETURNS { scalar_return_data_type | TABLE clr_table_type_definition }       [ WITH clr_function_option [ [,] ...n ] ]       [ AS ] EXTERNAL NAME method_specifier [ ; ]     method_specifier ::=       assembly_name.class_name.method_name     clr_table_type_definition ::=       ( { column_name data_type }[ ,...n ] )     clr_function_option ::=       [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]       | [ EXECUTE_AS_Clause ] 

where:

[ schema_name. ] function_name

Specifies the name of the CLR UDF to create.

@parameter_name [AS] [ type_schema_name. ] scalar_parameter_data_type

Defines zero or more parameters for the UDF:

@parameter_name

Specifies the name of a parameter for the function. Specify a parameter using an ampersand (@) as the first character. DEFAULT parameters cannot be specified for CLR UDFs.

[ type_schema_name. ] scalar_parameter_data_type

Specifies the parameter data type and optionally its schema. This can be any scalar data type supported by SQL Server except timestamp. The return value data type can also be a CLR UDT. char, varchar, and ntext data types cannot be specified for CLR scalar-valued UDFsuse nchar and nvarchar instead.

scalar_return_data_type

Specifies the data type of the return value of a scalar-valued UDF. This can be any scalar data type supported by SQL Server except text, ntext, image, and timestamp. The return value data type can also be a CLR UDT. char and varchar data types cannot be specified for CLR scalar-valued UDFsuse nchar and nvarchar instead.

clr_table_type_definition

Defines the CLR table returned from a table-valued UDF:

column_name

Specifies the name of the column in the table.

data_type

Specifies the data type of the columntimestamp and UDTs are not supported.

clr_function_option

Specifies the OnNullCall attribute of a scalar-valued function:

RETURNS NULL ON NULL INPUT

Specifies that SQL Server does not execute the function and returns NULL as the result of the function if any of the input arguments are NULL.

CALLED ON NULL INPUT

Specifies that SQL Server executes the function even if one or more input arguments are NULL.

If not specified, the default is CALLED ON NULL INPUT.

The CREATE FUNCTION value of the OnNullCall attribute takes precedence over the method's OnNullCall attribute if specified in the .NET code.

EXECUTE_AS_Clause

Specifies the security execution context for the UDF.

method_specifier

Specifies the method in the .NET Framework assembly that implements the UDF:

assembly_name

Specifies the name of the registered assembly that contains the method implementing the CLR UDF.

class_name

Specifies the name of the class in the assembly that implements the CLR UDF. The class name can be namespace-qualified, in which case it must be enclosed in brackets ([]).

method_name

Specifies a public static method of the class that implements the CLR UDF functionality.

4.6.3.2. ALTER FUNCTION

The ALTER FUNCTION T-SQL statement has been enhanced in SQL Server 2005 to let you modify a CLR UDF previously created using the CREATE FUNCTION statement. The ALTER FUNCTION syntax for creating CLR UDFs follows:

     ALTER FUNCTION [ schema_name. ] function_name       ( { @parameter_name [AS] [ type_schema_name. ] scalar_parameter_data_type }       [ ,...n ] )     RETURNS { scalar_return_data_type | TABLE <clr_table_type_definition> }       [ WITH clr_function_option [ ,...n ] ]       [ AS ] EXTERNAL NAME method_specifier     method_specifier ::=         [ assembly_schema. ] assembly_name.class_name.method_name     clr_table_type_definition :: =     ( { column_name data_type } [ ,...n ] )     clr_function_option ::=     [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]     | [ EXECUTE_AS_Clause ] 

The arguments are the same as for the CREATE FUNCTION statement discussed in the preceding section.

4.6.3.3. DROP FUNCTION

The DROP FUNCTION T-SQL statement removes one or more UDFs previously created using the CREATE FUNCTION statement. The SQL Server 2005 DROP FUNCTION statement is the same as in SQL Server 2000. It now supports removing CLR UDFs.

The DROP FUNCTION syntax is:

     DROP FUNCTION { [ schema_name. ] function_name } [ ,...n ] 

DROP FUNCTION will fail if T-SQL functions or views in the database created with SCHEMABINDING or indexed computed columns reference this function.

4.6.4. Stored Procedures

A stored procedure is a saved collection of T-SQL statements or a reference to a CLR method that optionally takes and returns arguments and returns one or more result sets of data. SQL Server 2005 stored procedures can be created directly from T-SQL statements or from methods in registered .NET Framework assemblies.

Stored procedures are created, updated, and removed using the CREATE PROCEDURE, ALTER PROCEDURE, and DROP PROCEDURE T-SQL statements. These statements have been enhanced in SQL Server 2005 to support CLR stored procedure management. The following subsections describe these enhancements.

4.6.4.1. CREATE PROCEDURE

The CREATE PROCEDURE T-SQL statement has been enhanced in SQL Server 2005 to let you create a CLR stored procedure from a stored procedure implemented as a method in a .NET Framework assembly. You must first register the assembly using the CREATE ASSEMBLY statement as you did earlier in the "Hello World Example" section. The enhancements supporting CLR stored procedures are highlighted in the CREATE PROCEDURE syntax that follows:

     CREATE PROC [ EDURE ] [schema_name.] procedure_name       [ { @parameter [ type_schema_name. ] data_type }       [ VARYING ] [ = default ] [ [ OUT [ PUT ] ] [ ,...n ]       [ WITH <procedure_option> [ ,...n ]       [ FOR REPLICATION ]     AS { sql_statement [ ...n ] | method_specifier }     procedure_option ::=       [ ENCRYPTION ]       [ RECOMPILE ]       [ EXECUTE_AS_Clause ]     sql_statement ::=       { [ BEGIN ] statements [ END ] }          
method_specifier
 ::=         EXTERNAL NAME 
assembly_name.class_name.method_name 

where the new arguments are as follows:

method_specifier

Specifies the method in the .NET Framework assembly that implements the stored procedure:

assembly_name

Specifies the name of the registered assembly that contains the method implementing the stored procedure.

class_name

Specifies the name of the class in the assembly that implements the stored procedure. The class name can be namespace-qualified, in which case it must be enclosed in brackets ([ ]).

method_name

Specifies the name of the public static method implementing the CLR stored procedure.

4.6.4.2. ALTER PROCEDURE

The ALTER PROCEDURE T-SQL statement has been enhanced in SQL Server 2005 to let you modify a CLR stored procedure previously created using the CREATE PROCEDURE statement. The enhancements supporting CLR stored procedures are highlighted in the ALTER PROCEDURE syntax that follows:

     ALTER PROC [ EDURE ] [schema_name.] procedure_name       [ { @parameter [ type_schema_name. ] data_type }       [ VARYING ] [ = default ] [ [ OUT [ PUT ] ] [ ,...n ]       [ WITH procedure_option [ ,...n ] ]       [ FOR REPLICATION ]     AS       { sql_statement [ ...n ] | method_specifier }     procedure_option ::=       [ ENCRYPTION ]       [ RECOMPILE ]       [ EXECUTE_AS_Clause ]     sql_statement ::=       { [ BEGIN ] statements [ END ] }     
method_specifier
 ::=        EXTERNAL NAME [ 
assembly_schema.
 ] 
assembly_name.class_name.method_name 

where the new arguments are as follows:

method_specifier

Specifies the method in the .NET Framework assembly that implements the stored procedure:

assembly_schema

Specifies the schema name for the assembly. If not specified, assembly_name must match an assembly in either the current user's schema or in the dbo schema.

assembly_name

Specifies the name of the registered assembly that contains the method that implements the stored procedure.

class_name

Specifies the name of the class in the assembly that implements the stored procedure. The class name can be namespace-qualified, in which case it must be enclosed in brackets ([ ]).

method_name

Specifies the name of the public static method implementing the CLR stored procedure.

4.6.4.3. DROP PROCEDURE

The DROP PROCEDURE T-SQL statement removes one or more stored procedures previously created using the CREATE PROCEDURE statement. The SQL Server 2005 DROP PROCEDURE statement is the same as in SQL Server 2000. It now supports removing CLR stored procedures.

The DROP PROCEDURE syntax is:

     DROP PROCEDURE { [ schema_name. ] procedure } [ ,...n ] 

4.6.4.4. Metadata

The sys.procedures catalog view contains a row for each stored procedure in the current database. The view inherits columns from the sys.objects and sys.all_objects catalog views, so you can limit the rows returned to CLR stored procedures by filtering on the type column, as shown in the following statement:

     SELECT * FROM sys.procedures     WHERE type='PC' 

The WHERE clause specifying the type PC returns CLR stored procedures. Specifying type P returns SQL stored procedures.

4.6.5. User-Defined Aggregate Functions

User-defined aggregate (UDA functions compute a value over a group in a result set. SQL Server 2005 UDA functions can be created directly from T-SQL statements or from a class in a registered .NET Framework assembly.

UDA functions are created and removed in SQL Server using the CREATE AGGREGATE and DROP AGGREGATE T-SQL statements. These statements have been enhanced in SQL Server 2005 to support CLR UDA function management. The following subsections describe the enhancements.

4.6.5.1. CREATE AGGREGATE

The CREATE AGGREGATE T-SQL statement creates a UDA function from an implementation by a class in a registered assembly. The assembly must first be registered using the CREATE ASSEMBLY statement.

The CREATE AGGREGATE syntax is:

     CREATE AGGREGATE [ schema_name. ] aggregate_name     ..(@parameter 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 } 

where:

[ schema_name. ] aggregate_name

Specifies the name of the CLR aggregate function to create.

@parameter input_sqltype

Specifies the name of a parameter in the CLR aggregate function. The name must be prefixed with an ampersand (@). A parameter can specify a constant only, and not the names of database objects such as table names and columns names.

EXTERNAL NAME assembly_name [ .class_name ]

Specifies the registered .NET Framework assembly and optionally the name of the class in the assembly that implements the CLR aggregate function. If class_name is not specified, it defaults to aggregate_name.

system_scalar_type

Specifies a SQL Server scalar data type for the input or return value.

[ udt_schema_name. ] udt_type_name

The name of a CLR UDT in SQL Server. If the schema name is not specified, it defaults to the schema of the current user.

4.6.5.2. DROP AGGREGATE

The DROP AGGREGATE T-SQL statement removes a UDA function previously created using the CREATE AGGREGATE statement.

The DROP AGGREGATE syntax follows:

     DROP AGGREGATE [ schema_name. ] aggregate_name 

where:

[ schema_name. ] aggregate_name

Specifies the name of the UDA function to remove.

The DROP AGGREGATE statement does not execute if there are views, functions, or stored procedures created with schema binding that reference the UDA function.

4.6.6. User-Defined Types

SQL Server 2000 supports user-defined types (UDTs ), also known as alias types. You create these by using the sp_addtypes system stored procedure. They are derived from SQL Server built-in data types and optionally have integrity constraints called rules.

SQL Server 2005 extends UDT functionality by letting you define CLR UDTs from a class in a registered .NET Framework assembly. A CLR UDT can store multiple items and expose methods, properties, and attributes. You can use a UDT as the data type for a column in a table, as a T-SQL variable, or as a parameter for stored procedures or functions.

UDTs are created and removed in SQL Server using the CREATE TYPE and DROP TYPE T-SQL statements. These statements have been enhanced in SQL Server 2005 to support CLR UDT management. The following subsections describe the enhancements.

4.6.6.1. CREATE TYPE

The CREATE TYPE statement has been enhanced in SQL Server 2005 to let you create a CLR UDT from a type implemented as a class or structure in a .NET Framework assembly. You must first register the assembly using the CREATE ASSEMBLY statement. The enhancements supporting CLR UDTs are highlighted in the CREATE TYPE syntax that follows:

     CREATE TYPE [ schema_name.  ] type_name        { FROM base_type        [ ( precision  [ , scale  ] )  ]       [ NULL | NOT NULL ]       | EXTERNAL NAME  assembly_name  [ . class_name  ]  } 

where:

[ schema_name. ] type_name

Specifies the name of the CLR UDT to create.

assembly_name

Specifies the name of the registered assembly that implements the CLR UDT.

class_name

Specifies the name of the class that implements the CLR UDT. The class name can be namespace-qualified, in which case it must be enclosed in brackets ([ ]).

4.6.6.2. DROP TYPE

The DROP TYPE T-SQL statement removes a UDT previously created using the CREATE TYPE statement. The SQL Server 2005 DROP TYPE statement is the same as in SQL Server 2000. It now supports removing CLR UDTs .

The DROP TYPE syntax is:

     DROP TYPE [ schema_name. ] type_name 

where:

[ schema_name. ] type_name

Specifies the name of the UDT to remove

DROP TYPE will not execute if there are tables in the database with columns of the UDT, if there are columns of the sql_variant data type that contain the UDT, or if there are functions, stored procedures, or triggers in the database created with the WITH SCHEMABINDING clause that use variables or parameters of the UDT.

4.6.6.3. Metadata

The sys.assembly_types catalog view contains a row for all CLR UDTs in the current database. The view inherits all columns from the sys.types catalog view.

4.6.7. Triggers

A trigger is a type of stored procedure that executes in response to one or more specific database events. DML triggers execute when data is modified using T-SQL DML statements such as INSERT, UPDATE, or DELETE. DDL triggers execute when database objects are modified using T-SQL DDL statements such as CREATE, ALTER, and DROP. SQL Server 2005 DML and DDL triggers can be created directly from T-SQL statements or from methods in a registered .NET Framework assembly.

Triggers are created, changed, and removed using the CREATE TRIGGER, ALTER TRIGGER, and DROP TRIGGER T-SQL statements. These statements have been enhanced in SQL Server 2005 to support CLR trigger management. The following subsections describe the enhancements.

4.6.7.1. CREATE TRIGGER

The CREATE TRIGGER T-SQL statement has been enhanced in SQL Server 2005 to let you create a CLR DML or DDL trigger from a trigger implemented as a method in a .NET Framework assembly. You must first register the assembly using the CREATE ASSEMBLY statement. The enhancements supporting CLR triggers are highlighted in this section.

The CREATE TRIGGER syntax for DML triggers is:

     CREATE TRIGGER [ schema_name. ]trigger_name       ON { TABLE | VIEW }       [ WITH dml_trigger_option [ ,...n ] ]       { FOR | AFTER | INSTEAD OF }       { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }       [ WITH APPEND ]       [ NOT FOR REPLICATION ]     AS { sql_statement [ ...n ] | EXTERNAL NAME method specifier }     dml_trigger_option ::=         [ ENCRYPTION ]         [ EXECUTE AS Clause ]     
method_specifier
 ::=          
assembly_name.class_name.method_name 

The CREATE TRIGGER syntax for DDL triggers is:

     CREATE TRIGGER trigger_name       ON { ALL SERVER | DATABASE }       [ WITH <ddl_trigger_option> [,...n ] ]       { FOR | AFTER } { event_type | event_group } [ ,...n ]     AS { sql_statement [ ...n ] | EXTERNAL NAME <method specifier> }     ddl_trigger_option ::=       [ ENCRYPTION ]       [ EXECUTE AS Clause ]     
method_specifier
 ::=         
assembly_name.class_name.method_name 

where the new arguments for both DML and DDL triggers are:

method_specifier

Specifies the method in the .NET Framework assembly that implements the trigger:

assembly_name

Specifies the name of the registered assembly that contains the method that implements the trigger.

class_name

Specifies the name of the class in the assembly that implements the CLR trigger. The class name can be namespace-qualified, in which case it must be enclosed in brackets ([]).

method_name

Specifies the public static method of the class that implements the CLR trigger functionality.

4.6.7.2. ALTER TRIGGER

The ALTER TRIGGER T-SQL statement has been enhanced in SQL Server 2005 to let you modify a CLR DML or DDL trigger previously created using the CREATE TRIGGER statement. The enhancements supporting CLR triggers are highlighted in this section.

The ALTER TRIGGER syntax for DML triggers follows:

     ALTER TRIGGER schema_name.trigger_name       ON ( TABLE | VIEW )       [ WITH dml_trigger_option [ ...,n ] ]       ( FOR | AFTER | INSTEAD OF )       { [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] }       [ NOT FOR REPLICATION ]     AS { sql_statement [ ...n ] | EXTERNAL NAME method specifier }     dml_trigger_option ::=       [ ENCRYPTION ]       [ EXECUTE AS Clause ]     
method_specifier
 ::=         
[ assembly_schema. ] assembly_name.class_name.method_name 

The ALTER TRIGGER syntax for DDL triggers follows:

     ALTER TRIGGER trigger_name       ON { DATABASE | ALL SERVER }       [ WITH ddl_trigger_option [ ...,n ] ]       { FOR | AFTER } { event_type [ ,...n ] | event_group }     AS { sql_statement | EXTERNAL NAME method specifier }     ddl_trigger_option ::=       [ ENCRYPTION ]       [ EXECUTE AS Clause ]     
method_specifier
 ::=       [ 
assembly_schema.
 ] 
assembly_name.class_name.method_name 

The new arguments for both DML and DDL triggers are the same as for the CREATE TRIGGER statement discussed in the preceding section.

4.6.7.3. DROP TRIGGER

The DROP TRIGGER T-SQL statement removes one or more DML or DDL triggers previously created using the CREATE TRIGGER statement. The SQL Server 2005 DROP TRIGGER statement is the same as in SQL Server 2000. It now supports removing CLR triggers.

The DROP TRIGGER syntax for DML triggers is:

     DROP TRIGGER schema_name.trigger_name [ ,...n ] 

The DROP TRIGGER syntax for DDL triggers is:

     DROP TRIGGER trigger_name [ ,...n ]     ON { DATABASE | ALL SERVER } 

4.6.7.4. Metadata

The sys.triggers catalog view contains a row for each trigger in the current database. You can limit the rows returned to CLR triggers by filtering on the type column, as shown in the following statement:

     SELECT * FROM sys.triggers     WHERE type='TA'