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.

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.

• Length, Precision, and Scale
  For some user-defined datatypes, you must specify additional parameters for lengh, precision, and scale.
• 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.
• Associate 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.
• Create User-Defined Datatype with IDENTITY Property
  Use sp_addtype to create a user-defined datatype with the IDENTITY property.
• Create IDENTITY Columns from User-Defined Datatypes
  You can create IDENTITY columns from user-defined datatypes that do not have the IDENTITY property.
• Drop a User-Defined Datatype
  Use sp_droptype to drop a user-defined datatype.