User defined data types created on the basis of system data
types.
For example you need to use same data type in
some of tables and you want to ensure that column has same data type, length
and nullability. At that time user defined data type is useful.
/* CREATE USER-DEFINED DATA TYPE */
CREATE TYPE ContactNo from varchar(20)
--OR
--EXEC
sp_addtype ContactNo, 'varchar(20)', 'NULL'
/* DROP
USER-DEFINED DATA TYPE */
EXEC sp_droptype 'ContactNo'
/* RENAME USER-DEFINED
DATA TYPE */
EXEC sp_rename 'ContactNo', 'ContactNumber', 'USERDATATYPE' |