SALES: 1-800-867-1380

CLR User-Defined Types

SQL Server gives you the ability to create database objects that are programmed against an assembly created in the.NET Framework common language runtime (CLR). Database objects that can take advantage of the rich programming model provided by the CLR include triggers, stored procedures, functions, aggregate functions, and types.

Note Note

The ability to execute CLR code is set to OFF by default in SQL Server. The CLR can be enabled by using the sp_configure system stored procedure.

Beginning with SQL Server 2005, you can use user-defined types (UDTs) to extend the scalar type system of the server, enabling storage of CLR objects in a SQL Server database. UDTs can contain multiple elements and can have behaviors, differentiating them from the traditional alias data types which consist of a single SQL Server system data type.

Because UDTs are accessed by the system as a whole, their use for complex data types may negatively impact performance. Complex data is generally best modeled using traditional rows and tables. UDTs in SQL Server are well suited to the following:

  • Date, time, currency, and extended numeric types

  • Geospatial applications

  • Encoded or encrypted data

The process of developing UDTs in SQL Server consists of the following steps:

  1. Code and build the assembly that defines the UDT.   UDTs are defined using any of the languages supported by the.NET Framework common language runtime (CLR) that produce verifiable code. This includes Visual C# and Visual Basic .NET. The data is exposed as fields and properties of a .NET Framework class or structure, and behaviors are defined by methods of the class or structure.

  2. Register the assembly.   UDTs can be deployed through the Visual Studio user interface in a database project, or by using the Transact-SQL CREATE ASSEMBLY statement, which copies the assembly containing the class or structure into a database.


    Note Note

    Before SQL Server 2005, UDTs created from .NET Framework assemblies were not supported. However, you can still use SQL Server alias data types by using sp_addtype. The CREATE TYPE syntax can be used for creating both native SQL Server user-defined data types and UDTs.

  4. Create tables, variables, or parameters using the UDT   Beginning with SQL Server 2005, a user-defined type can be used as the column definition of a table, as a variable in a Transact-SQL batch, or as an argument of a Transact-SQL function or stored procedure.

Creating a User-Defined Type

Describes how to create UDTs.

Registering User-Defined Types in SQL Server

Describes how to register and manage UDTs in SQL Server.

Working with User-Defined Types in SQL Server

Describes how to create queries using UDTs.

Accessing User-Defined Types in ADO.NET


Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

© 2015 Microsoft