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.
You must create user-defined datatypes in each database in which they are to 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.
The SAP ASE 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.
You can create user-defined datatypes that are the maximum datatype length (versions of Adaptive Server earlier than 15.7 SP121 restricted the length to the server page size). Use the @@maxvarlen global variable to check the maximum possible variable length allowed when creating a user-defined datatype.
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.
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.
The ANSI SQL compliance level for user-defined datatypes are a Transact-SQL extension.