sp_addtype

Creates a user-defined datatype.

Syntax

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

Parameters

Examples

Usage

  • sp_addtype creates a user-defined datatype and adds it to the systypes system table. Once a user-defined datatype is created, you can use it in create table and alter table statements and bind defaults and rules to it.

  • Build each user-defined datatype in terms of one of the SAP ASE-supplied datatypes, specifying the length or the precision and scale, as appropriate. You cannot override the length, precision, or scale in a create table or alter table statement.

  • A user-defined datatype name must be unique in the database, but user-defined datatypes with different names can have the same definitions.

  • If nchar or nvarchar is specified as the phystype, the maximum length of columns created with the new type is the length specified in sp_addtype multiplied by the value of @@ncharsize at the time the type was added.

  • If unichar or univarchar is specified as the phystype, the maximum length of columns created with the new type is the length specified in sp_addtype multiplied by the value of 2 at the time the type was added.

  • Each system type has a hierarchy, stored in the systypes system table. User-defined datatypes have the same datatype hierarchy as the physical types on which they are based. In a mixed-mode expression, all types are converted to a common type, the type with the lowest hierarchy.

    Use the following query to list the hierarchy for each system-supplied and user-defined type in your database:
    select name, hierarchy
    from systypes
    order by hierarchy
  • If a user-defined datatype is defined with the IDENTITY property, all columns created from it are IDENTITY columns. You can specify IDENTITY, NOT NULL, or neither in the create or alter table statement. Following are three different ways to create an IDENTITY column from a user-defined datatype with the IDENTITY property:
    create table new_table (id_col IdentType)
    create table new_table (id_col IdentType identity)
    create table new_table (id_col IdentType not null)
    When you create a column with the create table or alter table statement, you can override the null type specified with the sp_addtype system procedure:
    • Types specified as NOT NULL can be used to create NULL or IDENTITY columns.

    • Types specified as NULL can be used to create NOT NULL columns, but not to create IDENTITY columns.

    Note: If you try to create a null column from an IDENTITY type, the create or alter table statement fails.
See also:
  • create default, create rule, create table in Reference Manual: Commands

  • User-Defined Datatypes in Reference Manual: Building Blocks

Permissions

Any user can execute sp_addtype. Permission checks do not differ based on the granular permissions settings.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

InformationValues
Event

38

Audit option

exec_procedure

Command or access audited

Execution of a procedure

Information in extrainfo
  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – All input parameters

  • Proxy information – Original login name, if set proxy in effect

Related reference
sp_bindefault
sp_bindrule
sp_dboption
sp_droptype
sp_rename
sp_unbindefault
sp_unbindrule