As SQL Server DBAs, most of us are familiar with native column data types
like INTEGER, DATETIME, CHAR, DECIMAL, etc. From a system point of view, these
types are necessary so SQL Server can efficiently store and manipulate the data
stored in the column. But these intrinsic data types don’t really offer much
value when it comes to modeling real-world entities.
For example consider something most of us care a great deal about: annual
salary. In the real world, we know that we get paid on a regular basis in a real
number of dollars and cents. We don’t think of our salary in terms of a number
with 10 numeric places of which 2 are decimal. Mention that at your next party
and see if the person you’re talking to doesn’t suddenly need to be somewhere
Wouldn’t it be nice if we had a data type called “salary” that we could use
in our data base? Fortunately SQL Server provides this function by letting you
create your own “smart” data type based on one of the intrinsic types in the
system. In our example we can create a data type called “Salary”, define it as
Decimal(10, 2) and then use it in our DDL to create tables.
Create table Person (
PersonID int not null,
This doesn’t seem like a big deal until you think about all the places in the
data base where salary data types are used. Say you have 15 tables that store
salary information. It’s easy to define some of them as DECIMAL(15, 2),
DECIMAL(12, 2) or DECIMAL(10, 2) depending on the analyst who create and
maintains the table. On a per-table basis the different attribute definitions
don’t cause any trouble. But when you start using the tables together and want
to sum information from here into there, you can run into all kinds of trouble.
Eliminating the hard coded type and replacing it with something that makes
business sense can help eliminate trouble.
Let’s look at another useful example. SQL Server provides a data type called
UNIQUEIDENTIFIER that is meant to hold a value that is unique in all the world.
Cool, eh? The only problem is that SQL Server doesn’t automatically initialize
the column with the value like it does for IDENTITY columns. You can overcome
this slight drawback with a user-defined data type.
For example, I created a data type called GUID that uses UNIQUEIDENTIFIER as
its base type. Then I went one step further and defined a default value for GUID
as the function newID(). This function generates a UNIQUEIDENTIFIER each time
it’s called. Now when I create a table using GUID as the data type, it works
similar to IDENTITY in that the system automatically initializes the column.
Unlike IDENTITY, you can still provide the value for the GUID column if for some
reason you want to do so.
Another useful but somewhat clumsy feature is applying constraints to
user-defined data types. SQL Server works best when columns use CHECK
constraints are used to enforce column values but user-defined types are limited
to the older RULEs. Perhaps a future release of SQL Server will allow CHECK
constraints instead of RULES. In any event, suppose you have a column that holds
email addresses. You can create a user-defined type called EMAIL, create a rule
that validates the content with something like “@Email like '_%@_%._%'”, and
bind the rule to the type. This is admittedly simplistic validation for email
Finally, the some of the real power of user-defined data types becomes
apparent when you realize that you can use these types when you create stored
procedures and functions and use them to define the parameters. When used as
parameter types, you are guaranteed that the columns and parameters match in
length, precision and scale.
Enterprise manager is the easiest way to create user-defined types, defaults
and rules. However, you can also use the following system stored procedures and
statements to create and manage them.