sp_addtype
Creates a user-defined data type.
Syntax
sp_addtype [ @typename = ] type,
[ @phystype = ] system_data_type
[ , [ @nulltype = ] 'null_type' ]
[ , [ @owner = ] 'owner_name' ]
Arguments
[@typename =] type
Is the name of the user-defined data type. Data type names must follow the rules for identifiers and must be unique in each database. type is sysname, with no default.
[@phystype =] system_data_type
Is the physical, or Microsoft® SQL Server™-supplied, data type (decimal, int, and so on) on which the user-defined data type is based. system_data_type is sysname, with no default, and can be one of these values:
| 'binary( n )' | image | smalldatetime |
| Bit | int | smallint |
| 'char( n )' | 'nchar( n )' | text |
| Datetime | ntext | tinyint |
| Decimal | numeric | uniqueidentifier |
| 'decimal[ ( p [, s ] ) ]' | 'numeric[ ( p [ , s ] ) ]' | 'varbinary( n )' |
| Float | 'nvarchar( n )' | 'varchar( n )' |
| 'float( n )' | real |
Quotation marks are required around all parameters that have embedded blank spaces or punctuation marks. For more information about available data types, see Data Types.
- n
- Is a nonnegative integer indicating the length for the chosen data type.
- p
- Is a nonnegative integer indicating the maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. For more information, see decimal and numeric.
- s
- Is a nonnegative integer indicating the maximum number of decimal digits that can be stored to the right of the decimal point, and it must be less than or equal to the precision. For more information, see "decimal and numeric" in this volume.
[@nulltype =] 'null_type'
Indicates the way the user-defined data type handles null values. null_type is varchar(8), with a default of NULL, and must be enclosed in single quotation marks ('NULL', 'NOT NULL', or 'NONULL'). If null_type is not explicitly defined by sp_addtype, it is 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 statement or sp_dboption. Nullability should be explicitly defined.
Note The null_type parameter only defines the default nullability for this data type. If nullability is explicitly defined when the user-defined data type is used during table creation, it takes precedence over the defined nullability. For more information, see ALTER TABLE and CREATE TABLE.
[@owner =] 'owner_name'
Specifies the owner or creator of the new data type. owner_name is sysname. When not specified, owner_name is the current user.
Return Code Values
0 (success) or 1 (failure)
Result Sets
None
Remarks
A user-defined data type name must be unique in the database, but user-defined data types with different names can have the same definition.
Executing sp_addtype creates a user-defined data type and adds it to the systypes system table for a specific database, unless sp_addtype is executed with master as the current database. If the user-defined data type must be available in all new user-defined databases, add it to model. After a user data type is created, you can use it in CREATE TABLE or ALTER TABLE, as well as bind defaults and rules to the user-defined data type.
User-defined data types cannot be defined using the SQL Server timestamp data type.
Permissions
Execute permissions default to the public role.
Examples
A. Create a user-defined data type that does not allow null values
This example creates a user-defined data type named ssn (social security number) that is based on the SQL Server-supplied varchar data type. The ssn data type is used for columns holding 11-digit social security numbers (999-99-9999). The column cannot be NULL.
Notice that varchar(11) is enclosed in single quotation marks because it contains punctuation (parentheses).
USE master
EXEC sp_addtype ssn, 'VARCHAR(11)', 'NOT NULL'
B. Create a user-defined data type that allows null values
This example creates a user-defined data type (based on datetime) named birthday that allows null values.
USE master
EXEC sp_addtype birthday, datetime, 'NULL'
C. Create additional user-defined data types
This example creates two additional user-defined data types, telephone and fax, for both domestic and international telephone and fax numbers.
USE master
EXEC sp_addtype telephone, 'varchar(24)', 'NOT NULL'
EXEC sp_addtype fax, 'varchar(24)', 'NULL'