sp_addtype System Stored Procedure

Creates a user-defined datatype. Executing sp_addtype creates a user-defined datatype and adds it to the systypes system table. Once a user datatype is created, you can use it in the CREATE TABLE and ALTER TABLE statements, as well as bind defaults and rules to it.

Syntax

sp_addtype typename, phystype [, nulltype]

where

typename

Names the user datatype. Datatype names must follow the rules for identifiers and must be unique in each database.

phystype

Is the physical, or SQL Server - supplied, type (char, int, and so on) on which the user datatype is based. For details on available datatypes, see the Datatypes topic. (Quotation marks are required around all parameters that have embedded blank spaces or punctuation marks. For example, 'char(10)'.)

nulltype

Indicates how the user-defined datatype handles null value entries. Can be NULL or NOT NULL. The default nullability, if not explicitly defined by sp_addtype, will be set to the current default nullability. Use the GETANSINULL system function to determine the current default nullability, which can be adjusted by using the SET command or sp_dboption.. Nullability should be explicitly defined.

Note Remember that datatype nullability only defines the default nullability for this datatype. If nullability is explicitly defined when the user-defined datatype is used during table creation, it will take precedence over the defined nullability. For details, see the ALTER TABLE and CREATE TABLE statements.

Remarks

Define each user datatype in terms of one of the physical (SQL Server - supplied) datatypes, preferably specifying NULL (allow null entries) or NOT NULL (disallow them). A user-defined datatype name must be unique in the database, but user-defined datatypes with different names can have the same definition.

Examples

A.    Datatype That Does Not Allow Null Values

This example creates a user-defined datatype named ssn to be used for columns that hold social security numbers.

Notice that varchar(11) is enclosed in single quotation marks because it contains punctuation (parentheses).

sp_addtype ssn, 'VARCHAR(11)', 'NOT NULL'

B.    Datatype That Allows Null Values

This example creates a user-defined datatype (based on datetime) named birthday that allows null values.

sp_addtype birthday, datetime, NULL

Permission

Execute permission defaults to the public group.

Table Used

See Also