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.
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. See Chapter 14, “Defining Defaults and Rules for Data.”
Use sp_addtype to create user datatypes. It takes as parameters the name of the datatype being created, the Adaptive Server-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 Adaptive Server-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.
