Chapter 1: System Procedures

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

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 Adaptive Server-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
```

Datatypes with the IDENTITY property

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.
  
  If you try to create a null column from an IDENTITY type, the create or alter table statement fails.

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