sp_addtype

Description

Creates a user-defined datatype.

Syntax

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

Parameters

typename

is the name of the user-defined datatype. Type names must conform to the rules for identifiers and must be unique in each database.

phystype

is the physical or Adaptive Server-supplied datatype on which to base the user-defined datatype. You can specify any Adaptive Server datatype except timestamp.

The char, varchar, unichar, univarchar, nchar, nvarchar, binary, and varbinary datatypes expect a length in parentheses. If you do not supply one, Adaptive Server uses the default length of 1 character.

The float datatype expects a binary 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 decimal 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 a scale of 0.

Enclose physical types that include punctuation, such as parentheses or commas, within single or double quotes.

identity

indicates that the user-defined datatype has the IDENTITY property. Enclose the identity keyword within single or double quotes. You can specify the IDENTITY property only for numeric datatypes with a scale of 0.

IDENTITY columns store sequential numbers, such as invoice numbers or employee numbers, that are generated by Adaptive Server. The value of the IDENTITY column uniquely identifies each row in a table. IDENTITY columns are not updatable and do not allow null values.

nulltype

indicates how the user-defined datatype handles null value entries. Acceptable values for this parameter are null, NULL, nonull, NONULL, "not null", and "NOT NULL". Any nulltype that includes a blank space must be enclosed in single or double quotes.

If you omit both the IDENTITY property and the nulltype, Adaptive Server creates the datatype using the null mode defined for the database. By default, datatypes for which no nulltype is specified are created NOT NULL (that is, null values are not allowed and explicit entries are required). For compliance to the SQL standards, use the sp_dboption system procedure to set the allow nulls by default option to true. This changes the database’s null mode to NULL.

Examples

Example 1

Creates a user-defined datatype called ssn to be used for columns that hold social security numbers. Since the nulltype parameter is not specified, Adaptive Server creates the datatype using the database’s default null mode. Notice that varchar(11) is enclosed in quotation marks, because it contains punctuation (parentheses):

sp_addtype ssn, "varchar(11)"

Example 2

Creates a user-defined datatype called birthday that allows null values:

sp_addtype birthday, "datetime", null 

Example 3

Creates a user-defined datatype called temp52 used to store temperatures of up to 5 significant digits with 2 places to the right of the decimal point:

sp_addtype temp52, "numeric(5,2)"

Example 4

Creates a user-defined datatype called row_id with the IDENTITY property, to be used as a unique row identifier. Columns created with this datatype store system-generated values of up to 10 digits in length:

sp_addtype "row_id", "numeric(10,0)", "identity"

Example 5

Creates a user-defined datatype with an underlying type of sysname. Although you cannot use the sysname datatype in a create table, alter table, or create procedure statement, you can use a user-defined datatype that is based on sysname:

sp_addtype systype, sysname

Usage


Datatypes with the IDENTITY property

Permissions

Any user can execute sp_addtype.

Auditing

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

Event

Audit option

Command or access audited

Information in extrainfo

38

exec_procedure

Execution of a procedure

  • 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

See also

Commands create default, create rule, create table

Datatypes User-defined datatypes

System procedures sp_bindefault, sp_bindrule, sp_dboption, sp_droptype, sp_rename, sp_unbindefault, sp_unbindrule