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.