Share via

  • Article

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 MicrosoftVisual 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 module 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 SimpleUdt.

SELECT OBJECT_SCHEMA_NAME (referencing_id) AS referencing_schema_name,
    OBJECT_NAME(referencing_id) AS referencing_name,
    COL_NAME(referencing_id, referencing_minor_id) AS column_name,
    is_caller_dependent,
    is_ambiguous
FROM sys.sql_expression_dependencies
WHERE referenced_id = TYPE_ID('SimpleUdt')
    AND referenced_class = 6 
    AND OBJECTPROPERTY(referencing_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 SimpleUdt.

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_expression_dependencies AS d
JOIN sys.objects AS o
ON o.object_id = d.referencing_id
WHERE referenced_id = TYPE_ID('SimpleUdt')
AND referenced_class = 6 
AND OBJECTPROPERTY(o.object_id, 'IsCheckCnst')=1;

The following example retrieves the names of modules whose definitions reference user-defined type SimpleUdt.

USE AdventureWorks;
GO
SELECT referencing_schema_name, referencing_entity_name, referencing_id, referencing_class_desc, is_caller_dependent
FROM sys.dm_sql_referencing_entities ('SimpleUdt', 'TYPE');
GO

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');