User-Defined Data Types in Microsoft SQL Server 2008

A user-defined data type provides you with a convenient way to standardize the usage of native data types for columns that have the same domain of possible values. For example, when you store many e-mail addresses in different tables. Since there is no single, definitive way to store e-mail addresses it is hard to maintain consistency. You might store it as a varchar(30) in Customers table and as a varchar(50) in the Newsletter table. We can however create a user-defined data type emailaddress and use it in any table that keeps track of e-mail addresses to ensure that they all use the same native data type.

Our first user-defined data type

We’ll start by creating a new data type emailaddress:

CREATE TYPE emailaddress FROM varchar(30) NOT NULL;

We can now use the newly created data type as we would use a native data type. When the table is created, internally the emailaddress data type is known to be a varchar(30).

CREATE TABLE customer
(
   Id            smallint       NOT NULL,
   FirstName     varchar(50)    NOT NULL,
   LastName      varchar(50)    NOT NULL,
   Phone         varchar(50)    NOT NULL,
   Email         emailaddress   NOT NULL
)

Behind the scenes

We can gather more information about the columns in our tables by querying the catalog view sys.columns. We’ll use a basic query that shows us two columns in sys.columns, one containing a number that represents the underlying system data type and another containing a number that represents the data type that was used when the table was created.

SELECT column_id, name, system_type_id, user_type_id,
type_name(user_type_id) as user_type_name, max_length
FROM sys.columns WHERE object_id = object_id('customer')

column_id	name	system_type_id	user_type_id	user_type_name	max_length
1	Id	52	52	smallint	2
2	FirstName	167	167	varchar	50
3	LastName	167	167	varchar	50
4	Phone	167	167	varchar	50
5	Email	167	257	emailaddress	30

We can see that both the Phone column and the Email column have the same system_type_id, although the Email column shows that the user_type_id is a user-defined data type. This user-defined type is resolved when the table is created and can’t be dropped or changed as long as a table is using it. Once declared, a user-defined data type is static and immutable, so no inherent performance penalty occurs in using a user-defined type instead of their native variant.

The usage of these data types can make your database more consistent and clear. SQL Server implicitly converts between compatible columns of different types. Currently the user-defined types don’t support subtyping or inheritance, not do they allow a DEFAULT value or a CHECK constraint to be declared as part of de user-defined type itself.

30. October 2011 by Jeroen Verhulst
Categories: MS SQL Server, Uncategorized | 4 comments