Creates an alias data type.
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use CREATE TYPE instead.
Applies to: SQL Server (SQL Server 2008 through current version).
An alias data type name must be unique in the database, but alias data types with different names can have the same definition.
Executing sp_addtype creates an alias data type that appears in the sys.types catalog view for a specific database. If the alias data type must be available in all new user-defined databases, add it to model. After an alias data type is created, you can use it in CREATE TABLE or ALTER TABLE, and also bind defaults and rules to the alias data type. All scalar alias data types that are created by using sp_addtype are contained in the dbo schema.
Alias data types inherit the default collation of the database. The collations of columns and variables of alias types are defined in the Transact-SQL CREATE TABLE, ALTER TABLE and DECLARE @local_variable statements. Changing the default collation of the database applies only to new columns and variables of the type; it does not change the collation of existing ones.
For backward compatibility purposes, the public database role is automatically granted REFERENCES permission on alias data types that are created by using sp_addtype. Note when alias data types are created by using the CREATE TYPE statement instead of sp_addtype, no such automatic grant occurs.
Alias data types cannot be defined by using the SQL Server timestamp, table, xml, varchar(max), nvarchar(max) or varbinary(max) data types.
A. Creating an alias data type that does not allow for null values
The following example creates an alias 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; GO EXEC sp_addtype ssn, 'varchar(11)', 'NOT NULL'; GO
B. Creating an alias data type that allows for null values
The following example creates an alias data type (based on datetime) named birthday that allows for null values.
USE master; GO EXEC sp_addtype birthday, datetime, 'NULL';
C. Creating additional alias data types
The following example creates two additional alias data types, telephone and fax, for both domestic and international telephone and fax numbers.
USE master; GO EXEC sp_addtype telephone, 'varchar(24)', 'NOT NULL'; GO EXEC sp_addtype fax, 'varchar(24)', 'NULL'; GO