Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

User-defined Data Type Basics

By Don Frazier,

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.


Total article views: 8093 | Views in the last 30 days: 7
Related Articles

user defined functions & columns

user defined functions & columns


Average Salary

Average Salary


Creating and Managing User-Defined Roles in SQL Server 2012

SQL Server 2012 introduces user-defined server roles. These user-defined server roles are similar to...


Salary Survey

SQL Server Magazine has just published its annual salary survey.  I don’t really recall how they did...


The User Defined Server Roles in SQL Server 2012

This article describes how to create user defined server roles and use stored procedures and queries...