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

else.

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.

For example:

Create table Person (

PersonID int not null,

AnnualSalary Salary)

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

addresses.

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.

CREATE RULE

ALTER RULE

DROP RULE

SP_BINDRULE

SP_BINDDEFAULT

CREATE DEFAULT

DROP DEFAULT