Implementing User-defined Types
This topic describes how to create and drop common language runtime (CLR) user-defined types in SQL Server.
Creating User-defined Types
To create a user-defined type in SQL Server, the following steps must be performed in order:
- Define the user-defined type as a class or structure in a language supported by the Microsoft .NET Framework. For more information about how to program types in the CLR, see CLR User-Defined Types. Then, compile the class or structure to build an assembly in the .NET Framework using the appropriate language compiler.
- Register the assembly in SQL Server by using the CREATE ASSEMBLY statement. For more information about assemblies in SQL Server, see Assemblies (Database Engine).
- Create the type that references the registered assembly.
Note
Deploying a SQL Server Project in Microsoft Visual Studio registers an assembly in the database that was specified for the project. Deploying the project also creates CLR user-defined types in the database for all class definitions annotated with the SqlUserDefinedType attribute. For more information, see Deploying CLR Database Objects.
Note
By default, the ability of SQL Server to execute CLR code is set to OFF. You can create, modify, and drop database objects that reference managed code modules, but these references will not execute in SQL Server unless the clr enabled Option is enabled by using sp_configure.
To create, modify, or drop an assembly
To create a user-defined type
Dropping User-defined Types
To drop a user-defined type
Note
User-defined types cannot be modified after they are created, because changes could invalidate data in tables or indexes. To modify a type, you must either drop the type and then re-create it, or issue an ALTER ASSEMBLY statement by using the WITH UNCHECKED DATA clause.
You cannot drop a user-defined type until all references to that type have been removed. These references can include the following:
- Columns defined on the type.
- Computed columns and CHECK constraints whose expressions reference the type.
- Schema-bound views and functions with expressions in their definitions that reference the type.
- Parameters of functions and stored procedures.
To find columns dependent on a user-defined type
The following example retrieves metadata about columns defined on user-defined type ComplexNumber.
SELECT * FROM sys.columns
WHERE user_type_id = TYPE_ID('ComplexNumber');
The following example retrieves limited metadata for least-privileged users about columns defined on user-defined type ComplexNumber.
SELECT * FROM sys.column_type_usages
WHERE user_type_id = TYPE_ID('ComplexNumber');
To find computed column expressions, CHECK constraint expressions, and schema-bound view and function expressions dependent on a user-defined type
The following example retrieves the names of computed columns (and their tables) with a dependency on user-defined type ComplexNumber.
SELECT OBJECT_NAME(object_id) AS object_name,
COL_NAME(object_id, column_id) AS column_name
FROM sys.sql_dependencies
WHERE referenced_major_id = TYPE_ID('ComplexNumber')
AND class = 2
AND OBJECTPROPERTY(object_id, 'IsTable')=1;
The following example retrieves the names of CHECK constraints (and the objects on which they are defined) with a dependency on user-defined type ComplexNumber.
SELECT SCHEMA_NAME(o.schema_id) AS schema_name,
OBJECT_NAME(o.parent_object_id) AS table_name,
OBJECT_NAME(o.object_id) AS constraint_name
FROM sys.sql_dependencies AS d
JOIN sys.objects AS o
ON o.object_id = d.object_id
WHERE referenced_major_id = TYPE_ID('ComplexNumber')
AND class = 2
AND OBJECTPROPERTY(o.object_id, 'IsCheckCnst')=1;
The following example retrieves the names of schema-bound functions and views whose definitions reference user-defined type ComplexNumber.
Note
SQL Server does not maintain dependency metadata between user-defined types and their use in the bodies of stored procedures, triggers, non-schema-bound functions or non-schema-bound views.
SELECT SCHEMA_NAME(o.schema_id) AS dependent_object_schema,
OBJECT_NAME(o.object_id) AS dependent_object_name,
o.type_desc AS dependent_object_type,
d.class_desc AS kind_of_dependency,
d.referenced_major_id AS referenced_object
FROM sys.sql_dependencies AS d
JOIN sys.objects AS o
ON d.object_id = o.object_id
AND o.type IN ('FN','IF','TF', 'V')
WHERE d.class = 2
AND d.referenced_major_id IN (TYPE_ID('ComplexNumber'))
ORDER BY dependent_object_schema, dependent_object_name;
To find parameters dependent on a user-defined type
The following example retrieves the names of parameters (and the objects to which they belong) defined on user-defined type ComplexNumber.
SELECT OBJECT_NAME(object_id) AS object_name,
NULL AS procedure_number,
name AS param_name,
parameter_id AS param_num,
TYPE_NAME(p.user_type_id) AS type_name
FROM sys.parameters AS p
WHERE p.user_type_id = TYPE_ID('ComplexNumber')
ORDER BY object_name, procedure_number, param_num;
The following example retrieves limited metadata for least-privileged users about parameters defined on user-defined type ComplexNumber.
SELECT * FROM sys.parameter_type_usages
WHERE user_type_id = TYPE_ID('ComplexNumber');
See Also
Concepts
Working with CLR User-defined Types
Other Resources
Help and Information
Getting SQL Server 2005 Assistance
Change History
| Release | History |
|---|---|
14 April 2006 |
|