Along with all of the default datatypes, you can also create your own. No, you can't define something original. User defined datatypes need to be defined from a system datatype.

User defined datatypes are one of the most powerful constructs that you can utilize in your schema. However, they are rarely used mainly due to lack of understanding. User defined datatypes can give you an excellent source of documentation within your schema, but that is the least beneficial feature. User defined datatypes show their true power when combined with rules and defaults.

Many databases contain a state column for customer information. This is generally a 2 character column. It is perfectly legal to define this as a char(2) column. But, char(2) does not hold much meaning. Instead you could create a user defined datatype that is a char(2).

I always use a set of standard naming conventions for objects. For user defined datatypes I use, udt_.

Creating the user defined datatype is accomplished using the system stored procedure sp_addtype. To create the state user defined datatype we would execute the following:

exec sp_addtype udt_state, 'char(2)', 'not null'

This would create a character datatype called udt_state that accepts as many as 2 characters and does not allow nulls. Once created, this datatype can be used just like any of the system datatypes for variables or table definitions. Using the udt_state datatype in a table definition tells everyone what type of data is stored in that column.

One of the most common uses for a user defined datatypes would be for Yes/No, On/Off, etc. columns. In many applications a user can select from one of a set of options. Using a user defined datatype gives a level of functionality that can't be achieved easily otherwise.

As an illustration consider the following scenario. Company X has a database where they store customer preferences. Preferences are stored for dozens of products. Each product has its preferences stored in a separate table due to the unique nature of many of the characteristics. For simplicity, management has decided that the initial phases are only going to track 3 options for each of 10 preferences on 40 products. The options can be like, dislike, and undecided. Each of the product tables is defined with some identifying information and 10 columns for the preferences. This gives the database 400 columns that can have any one of 3 different values. Further they have decided that the default value will be U for Undecided. Company Y, a competitor, has a similar need and defines the same business rules as Company X.

The database designer in Company X decides to use a char(1) column that is not nullable for each of the 400 columns. The designer creates the tables in the database and then adds the default of U to each of the 400 columns.

The database designer in Company Y decides to create a user defined datatype called udt_preference with a datatype of char(1) that does not allow nulls. Since the designer needs the columns to default to U, a default is created. After the tables are created, the default is bound to the datatype.

What was the difference here? The database designer in Company X had to add the default 400 separate times. The database designer in Company Y bound it to the user defined datatype once and it was automatically applied to all 400 columns with that user defined datatype thus saving 399 commands.

Now consider that two months later each company decides that the default value should now be yes. The database designer in Company X will have to create 400 commands to drop the defaults and 400 commands to add the new default value. The database designer in Company Y only has to execute 4 commands: unbind the default, drop it, recreate the default, bind the default.

The scenario above gives you a good illustration of the power that user defined datatypes will bring to your database schemas. Not only do they provide a straightforward documentation method, but they also provide a single location to apply rules and defaults en mass to your database.

SQL Server 7.0 Datatypes 1 2 3 4 5 6