User-Defined Data Types

A user-defined data type (UDT) provides a convenient way for you to guarantee consistent use of underlying native data types for columns known to have the same domain of possible values. For example, perhaps your database will store various phone numbers in many tables. Although no single, definitive way exists to store phone numbers, in this database consistency is important. You can create a phone_number UDT and use it consistently for any column in any table that keeps track of phone numbers to ensure that they all use the same data type. Here's how to create this UDT:

CREATE TYPE phone_number FROM varchar(20) NOT NULL;

And here's how to use the new UDT when you create a table:

CREATE TABLE customer ( cust_id             smallint        NOT NULL, cust_name           varchar(50)     NOT NULL, cust_addr1          varchar(50)     NOT NULL, cust_addr2          varchar(50)     NOT NULL, cust_city           varchar(50)     NOT NULL, cust_state          char(2)         NOT NULL, cust_postal_code    varchar(10)     NOT NULL, cust_phone          phone_number    NOT NULL, cust_fax            varchar(20)     NOT NULL, cust_email          varchar(30)     NOT NULL, cust_web_url        varchar(100)    NOT NULL )

When the table is created, internally the cust_phone data type is known to be varchar(20). Notice that both cust_phone and cust_fax are varchar(20), although cust_phone has that declaration through its definition as a UDT.

Information about the columns in your tables is available through the catalog view sys.columns, which we'll look at in more detail in the section on internal storage later in this chapter. For now, we'll just look at a basic query to show us two columns in sys.columns, one containing a number representing the underlying system data type and one containing a number representing the data type used when creating the table. The following query selects all the rows from sys.columns and displays the column_id, the column name, the data type values, and the maximum length, and the results are shown immediately after:

SELECT column_id, name, system_type_id, user_type_id,         type_name(user_type_id) as user_type_name, max_length FROM sys.columns WHERE object_id=object_id('customer'); column_id   type_name        system_type_id user_type_id user_type_name max_length ----------- ---------------- -------------- ------------ -------------- ---------- 1           cust_id          52             52           smallint       2 2           cust_name        167            167          varchar        50 3           cust_addr1       167            167          varchar        50 4           cust_addr2       167            167          varchar        50 5           cust_city        167            167          varchar        50 6           cust_state       175            175          char           2 7           cust_postal_code 167            167          varchar        10 8           cust_phone       167            257          phone_number   20 9           cust_fax         167            167          varchar        20 10          cust_email       167            167          varchar        30 11          cust_web_url     167            167          varchar        100

You can see that both the cust_phone and cust_fax columns have the same system_type_id value, although the cust_phone column shows that the user_type_id is a UDT (user_type_id = 257). The type is resolved when the table is created, and the UDT can't be dropped or changed as long as one or more tables are currently using it. Once declared, a UDT is static and immutable, so no inherent performance penalty occurs in using a UDT instead of the native data type.

The use of UDTs can make your database more consistent and clear. SQL Server implicitly converts between compatible columns of different types (either native types or UDTs of different types).

Currently, UDTs don't support the notion of subtyping or inheritance, nor do they allow a DEFAULT value or a CHECK constraint to be declared as part of the UDT itself. These powerful object-oriented concepts will likely make their way into future versions of SQL Server. These limitations notwithstanding, UDT functionality is a dynamic and often underused feature of SQL Server.

CLR Data Types

SQL Server 2005 introduces the ability to integrate .NET programming into SQL Server objects, allowing you to create such CLR-based objects such as stored procedures, functions, and triggers using any .NET language. You can even define your data types with .NET, which allows you to have a UDT that is more than just a new name for a preexisting construct. Inside Microsoft SQL Server 2005: T-SQL Programming has an extensive discussion of creating CLR-based UDTs, including several code examples, so I will refer you to that volume for that discussion.