Share via

CLR User-Defined Types 

  • Article

Microsoft SQL Server 2005 adds support for user-defined types (UDTs) implemented with the Microsoft .NET Framework common language runtime (CLR). The CLR is integrated into SQL Server, and this new mechanism enables you to extend the type system of the database. UDTs provide user extensibility of the SQL Server data type system, and also the ability to define complex structured types.

UDTs can provide two key benefits from an application architecture perspective:

  • Strong encapsulation (both in the client and the server) between the internal state and the external behaviors.

  • Deep integration with other related server features. Once you define your own UDT, you can use it in all contexts where you can use a system type in SQL Server, including column definitions, and as variables, parameters, function results, cursors, triggers, and replication.

To develop a UDT in SQL Server 2005, you must first code and build the assemby that defines the UDT in a .NET Framework programming language, such as Microsoft Visual C# or Microsoft Visual Basic. The fields and properties of the class or structure defined in the assembly expose the data of the UDT, and the methods of the class or structure define its behavior. Once the assembly is built it must be registered in SQL Server. This can be done through the Microsoft Visual Studio integrated development environment or by using the Transact-SQL CREATE ASSEMBLY statement, which copies the assembly defining the UDT into a database. Once the assembly is registered and copied into a database, you can use the Transact-SQL CREATE TYPE statement to create a UDT and expose its members. UDTs exist only in the context of the database in which they were registered, and have no dependencies on the external assemblies from which they were created. The UDT can then be used as the column definition of a table, a variable in a Transact-SQL batch, or an argument of a Transact-SQL function or stored procedure.

For more information, see the topic Creating and Using User-Defined Types and the topic "CLR User-Defined Types" in SQL Server 2005 Books Online.