User-defined datatypes are built from the system datatypes and from the sysname or longsysname user-defined datatypes. After you create a user-defined datatype, you can use it to define columns, parameters, and variables. Objects that are created from user-defined datatypes inherit the rules, defaults, null type, and IDENTITY property of the user-defined datatype, as well as inheriting the defaults and null type of the system datatypes on which the user-defined datatype is based.
A user-defined datatype must be created in each database in which it will be used. Create frequently used types in the model database. These types are automatically added to each new database (including tempdb, which is used for temporary tables) as it is created.
Adaptive Server allows you to create user-defined datatypes, based on any system datatype, using sp_addtype. You cannot create a user-defined datatype based on another user-defined datatype, such as timestamp or the tid datatype in the pubs2 database.
The sysname and longsysname datatypes are exceptions to this rule. Though sysname and longsysname are user-defined datatypes, you can use them to build user-defined datatypes.
User-defined datatypes are database objects. Their names are case-sensitive and must conform to the rules for identifiers.
You can bind rules to user-defined datatypes with sp_bindrule and bind defaults with sp_bindefault.
By default, objects built on a user-defined datatype inherit the user-defined datatype’s null type or IDENTITY property. You can override the null type or IDENTITY property in a column definition.
Use sp_rename to rename a user-defined datatype.
Use sp_droptype to remove a user-defined datatype from a database.
You cannot drop a datatype that is already in use in
a table.
Use sp_help to display information about the properties of a system datatype or a user-defined datatype. You can also use sp_help to display the datatype, length, precision, and scale for each column in a table.
