Creating and Using User-Defined Types
SQL Server 2005 gives you the ability to create database objects that are programmed against an assembly created in the Microsoft .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.
The ability to execute CLR code is set to OFF by default in SQL Server 2005. The CLR can be enabled by using the sp_configure system stored procedure.
The introduction of user-defined types (UDTs) in Microsoft SQL Server 2005 allows you to extend the server's scalar type system, 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.
Since 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 2005 are well suited to the following:
Date, time, currency, and extended numeric types
Encoded or encrypted data
The process of developing UDTs in SQL Server 2005 consists of the following steps:
Code and build the assembly that defines the UDT UDTs are defined using any of the languages supported by the Microsoft .NET Framework common language runtime (CLR) that produce verifiable code. This includes Microsoft Visual C# and Microsoft Visual Basic .NET. The data is exposed as fields and properties of a .NET class or structure, and behaviors are defined by methods of the class or structure.
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.
Create the UDT in SQL Server Once an assembly is loaded into a host database, you use the Transact-SQL CREATE TYPE statement to create a UDT and expose the members of the class or structure as members of the UDT. UDTs exist only in the context of a single database, and, once registered, have no dependencies on the external files from which they were created.
UDTs created from .NET Framework assemblies are not supported in previous versions of SQL Server. However, SQL Server alias data types are still supported in SQL Server 2005 by using sp_addtype. The CREATE TYPE syntax can be used for creating both native SQL Server user-defined data types and UDTs.
Create tables, variables, or parameters using the UDT In 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. For additional information, see "CLR User-Defined Types" and "Working with CLR User-Defined Types" in SQL Server 2005 Books Online.
In This Section
- 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
Describes how to work with UDTs using the .NET Framework Data Provider for SQL Server in ADO.NET.
Describes the ADO.NET architecture and components and how to use them to access existing data sources as well as to manage application data.
- ADO.NET Architecture
Describes the components of ADO.NET and the roles they play in the ADO.NET architecture.