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.
sp_addtype typename, phystype [, nulltype]
where
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.
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.
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'
This example creates a user-defined datatype (based on datetime) named birthday that allows null values.
sp_addtype birthday, datetime, NULL
Execute permission defaults to the public group.
CREATE DEFAULT | sp_droptype |
CREATE RULE | sp_rename |
CREATE TABLE | sp_unbindefault |
sp_bindefault | sp_unbindrule |
sp_bindrule |