A Transact-SQL enhancement to SQL allows you to name and design your own datatypes to supplement the system datatypes. A user-defined datatype is defined in terms of system datatypes. You can give one name to a frequently used datatype definition. This makes it easy for you to custom fit datatypes to columns.
To use
a user-defined datatype in more than one database, create it in
the model database. The user-defined datatype
definition then becomes known to all new databases you create.
Once you define a datatype, it can be used 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. For more about this topic, see Chapter 14, “Defining Defaults and Rules for Data.”
Use sp_addtype to create user datatypes. It takes as parameters the name of the user 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.
The syntax for sp_addtype is:
sp_addtype datatypename, phystype [ (length) | (precision [, scale] ) ] [, "identity" |nulltype]
Define 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.
