Creating user-defined datatypes

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. 
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.
Here is the syntax for sp_addtype:

sp_addtype datatypename, 
     phystype [ (length) | (precision [, scale] ) ]
     [, "identity" |nulltype]

Here is how tid was defined:

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.

Specifying length, precision, and scale

When you build a user-defined datatype based upon certain Adaptive Server datatypes, you must specify additional parameters:

• The char, nchar, varchar, nvarchar, binary, and varbinary datatypes expect a length in parentheses. If you do not supply one, Adaptive Server assumes the default length of 1 character.
• The float datatype expects a precision in parentheses. If you do not supply one, Adaptive Server uses the default precision for your platform.
• The numeric and decimal datatypes expect a precision and scale, in parentheses and separated by a comma. If you do not supply them, Adaptive Server uses a default precision of 18 and scale of 0.

You cannot change the length, precision, or scale specification when you include the user-defined datatype in a create table statement.

Specifying null type

The null type determines how the user-defined datatype treats nulls. You can create a user-defined datatype with a null type of "null", "NULL", "nonull", "NONULL", "not null", or "NOT NULL". bit and IDENTITY types do not allow null values.
If you omit the null type, Adaptive Server uses the null mode defined for the database (by default, NOT NULL). For compatibility with the SQL standards, use sp_dboption to set the allow nulls by default option to true.
You can override the null type when you include the user-defined datatype in a create table statement.

Associating rules and defaults with user-defined datatypes

Once you have created a user-defined datatype, use sp_bindrule and sp_bindefault to associate rules and defaults with the datatype. Use sp_help to print a report that lists the rules, defaults, and other information associated with the datatype.
Rules and defaults are discussed in Chapter 12, "Defining Defaults and Rules for Data." For complete information on, and syntax for, system procedures, see the Reference Manual.

Creating a user-defined datatype with the IDENTITY property

To create a user-defined datatype with the IDENTITY property, use sp_addtype. The new type must be based on a physical type of numeric with a scale of 0:

sp_addtype typename, "numeric (precision, 0)", "identity"

The following example creates a user-defined type, IdentType, with the IDENTITY property:

sp_addtype IdentType, "numeric(4,0)", "identity"

When you create a column from an IDENTITY type, you can specify either identity or not null--or neither one--in the create or alter table statement. The column automatically inherits the IDENTITY property.
Here are three different ways to create an IDENTITY column from the IdentType user-defined type:

create table new_table (id_col IdentType)

drop table new_table

create table new_table (id_col IdentType identity)

drop table new_table

create table new_table (id_col IdentType not null)

drop table new_table

If you try to create a column that allows nulls from an IDENTITY type, the create table or alter table statement fails.

Creating IDENTITY columns from other user-defined datatypes

You can create IDENTITY columns from user-defined datatypes that do not have the IDENTITY property. The user-defined types must have a physical datatype of numeric with a scale of 0 and must be defined as not null.

Dropping a user-defined datatype

To drop a user-defined datatype, execute sp_droptype:

sp_droptype typename

You cannot drop a datatype that is already in use in any table.