User-Defined Datatypes

User-defined datatypes (UDDTs) allow you to define custom datatypes based on the system datatypes. For example, you could create a name datatype defined as varchar(30). Any columns you create with the name datatype would automatically be varchar(30). This helps create consistent fields across the database, which is especially helpful for join columns to ensure the absence of datatype mismatches. To create more complex UDDTs, you can bind rules and defaults (see Chapter 14, "Implementing Data Integrity") to them to add even more functionality.

To create and drop UDDTs, use sp_addtype and sp_droptype:

Sp_addtype phone, 'char(13)' 
Sp_droptype phone

You can also add UDDTs through Enterprise Manager. Figure 12.1 shows the UDDT Properties box.

If the system datatype expression requires parentheses, you must surround it with quotes. You cannot drop a UDDT if any tables are using it. When you want to use a UDDT, simply specify it in place of a system datatype in your CREATE TABLE command. Listing 12.2 illustrates using the phone UDDT when creating a table.

Listing 12.2 Creating a Table Using a UDDT

CREATE TABLE call_me
(call_id int identity,
 name VARCHAR(20),
 phone_no phone)

The datatype allows you to define the storage for data within a column. The following section will look at creating tables to define the columns and group them in a logical fashion.