Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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.

CREATE RULE
ALTER RULE
DROP RULE
SP_BINDRULE
SP_BINDDEFAULT
CREATE DEFAULT
DROP DEFAULT

Total article views: 8047 | Views in the last 30 days: 11
 
Related Articles
FORUM

user defined functions & columns

user defined functions & columns

FORUM

Average Salary

Average Salary

BLOG

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...

BLOG

Salary Survey

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

ARTICLE

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...

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones