User-Defined Datatypes

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.