Export (0) Print
Expand All

Implementing User-defined Types

Updated: 14 April 2006

This topic describes how to create and drop common language runtime (CLR) user-defined types in SQL Server.

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.
ms179298.note(en-US,SQL.90).gifNote:
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.

ms179298.note(en-US,SQL.90).gifNote:
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

To drop a user-defined type

ms179298.note(en-US,SQL.90).gifNote:
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.

ms179298.note(en-US,SQL.90).gifNote:
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');

Release History

14 April 2006

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

Community Additions

ADD
Show:
© 2014 Microsoft