User-Defined Datatypes

A Transact-SQL enhancement to SQL allows you to design your own datatypes to supplement the system datatypes. A user-defined datatype is defined in terms of system datatypes.

Note: To use a user-defined datatype in more than one database, create the datatype in the model database. You can then use the user-defined datatype definition in any new databases you create.

Once you define a datatype, you can use it as the datatype for any column in the database. For example, tid is used as the datatype for columns in several pubs2 tables: titles.title_id, titleauthor.title_id, sales.title_id, and roysched.title_id.

The advantage of user-defined datatypes is that you can bind rules and defaults to them for use in several tables.

Use sp_addtype to create user datatypes. It takes as parameters the name of the datatype being created, the SAP ASE-supplied datatype from which it is being built, and an optional null, not null, or identity specification.

You can build a user-defined datatype using any system datatype other than timestamp. User-defined datatypes have the same datatype hierarchy as the system datatypes on which they are based. Unlike SAP ASE-supplied datatypes, user-defined datatype names are case-sensitive.

For example, to define datatype tid:

sp_addtype tid, "char(6)", "not null" 

You must enclose a parameter within single or double quotes if it includes a blank or some form of punctuation, or if it is a keyword other than null (for example, identity or sp_helpgroup). In this example, quotes are required around char(6) because of the parentheses, but around “not null” because of the blank. They are not required around tid.

Related concepts
Defining Defaults and Rules for Data