Share via


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

CLR Programmability Samples

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

14 April 2006

New content:
  • Added the section "Dropping User-defined Types," with information about locating the dependencies on user-defined types.