User-Defined and Alias Data Types

User-defined types (UDTs) and alias types in SQL Server 2005 also present a means to enforce or ensure integrity, particularly domain integrity. A user-defined type is implemented in a .NET language, through a class of an assembly that executes in the common language runtime (CLR), as discussed in Chapter 11. An alias data type is based on a SQL Server native system type.

Alias types are easier to implement because they are an abstract of already existing system types. Their integrity utility becomes apparent when you need to store the same type of data in a column in a number of tables and you need to be certain that the columns across all the tables maintain the identical data type, length, and nullability.

The syntax to create an alias type is as follows:

 CREATE TYPE [ schema_name. ] type_name {     FROM base_type     [ ( precision [ , scale ] )  ]     [ NULL | NOT NULL ]   | EXTERNAL NAME assembly_name [ .class_name ] } [ ; ]

Here is an example of using an alias data type:

 USE SCRATCH GO CREATE TYPE dbo.Telephone     FROM nvarchar(10) NOT NULL;

You can now use the type “Telephone” in your application and the type will conform to the system type, length, and nullability of the underlying specification…and it will inherit default values and so on that you specified when creating the alias. Using Management Studio, the type will appear in your selection of Data Types when you add columns on the Modify option. Or you can use the alias type in T-SQL as follows:

 CREATE TABLE [CRM].[Phone](     [Phone] [dbo].[Telephone] NULL)