Microsoft® SQL Server™ 2008 All-In-One Desk Reference For Dummies®

II.3.3. Creating Your Own Data Types

In the previous sections of this chapter, we show you quite a number of different data types. Some are quite simple and some are rather complex. However, if none of these types is an exact fit to solve your problem, you can create specialized data types via the SQL Server, user-defined type feature.

NOTE

You can use the CREATE TYPE statement to define personalized alias data types. This essentially creates a synonym for the data type, which can be very useful when building your database.

For example, suppose your project has multiple database designers and that each of them is responsible for building some tables that will track addresses. Furthermore, imagine that you want to standardize all your address fields as variable length and character-based to hold up to 60 bytes (VARCHAR(60)).

You could rely on each designer to adhere to your request, but you're likely to be sorely disappointed. The odds are that each designer will implement his or her own interpretation of what an address should be. Some will choose fixed character fields, while others will use the VARCHAR type but at a different length than the 60 that you require.

Using the CREATE TYPE statement, here's how to enforce consistency for these fields:

CREATE TYPE ADDRESS FROM VARCHAR(60) NOT NULL

Now, your designers can use ADDRESS whenever they create a table that needs to track address information:

CREATE TABLE shipping_info ( ShippingID INT PRIMARY KEY NOT NULL, StreetAddress ...