A user-defined datatype (UDDT) provides a convenient way for you to guarantee consistent use of underlying native datatypes 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 UDDT and use it consistently for any column in any table that keeps track of phone numbers to ensure that they all use the same datatype. Here's how to create this UDDT:
EXEC sp_addtype phone_number, 'varchar(20)', 'not null'
And here's how to use the new UDDT when creating 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_zip varchar(10) NOT NULL, cust_phone phone_number, cust_fax varchar(20) NOT NULL, cust_email varchar(30) NOT NULL, cust_web_url varchar(20) NOT NULL)
When the table is created, internally the datatype of cust_phone 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 UDDT.
Here's how the customer table appears in the entries in the syscolumns table for this table:
SELECT colid, name, xtype, length, xusertype, offset FROM syscolumns WHERE id=object_id('customer') colid name xtype length xusertype offset ------ ---------- ----- ------ --------- ------ 1 cust_id 52 2 52 2 2 cust_name 167 50 167 -1 3 cust_addr1 167 50 167 -2 4 cust_addr2 167 50 167 -3 5 cust_city 167 50 167 -4 6 cust_state 175 2 175 4 7 cust_zip 167 10 167 -5 8 cust_phone 167 20 261 -6 9 cust_fax 167 20 167 -7 10 cust_email 167 30 167 -8 11 cust_web_u 167 20 167 -9
You can see that both the cust_phone and cust_fax columns have the same xtype (datatype), although the cust_phone column shows that the datatype is a UDDT ( xusertype = 261). The type is resolved when the table is created, and the UDDT can't be dropped or changed as long as one or more tables are currently using it. Once declared, a UDDT is static and immutable, so no inherent performance penalty occurs in using a UDDT instead of the native datatype.
The use of UDDTs can make your database more consistent and clear. SQL Server implicitly converts between compatible columns of different types (either native types or UDDTs of different types).
Currently, UDDTs don't support the notion of subtyping or inheritance, nor do they allow a DEFAULT value or CHECK constraint to be declared as part of the UDDT itself. These powerful object-oriented concepts will likely make their way into future versions of SQL Server. These limitations not withstanding, UDDT functionality is a dynamic and often underused feature of SQL Server.