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.

Figure 12.1. Using Enterprise Manager to create a UDDT.

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.