Create User-Defined Data Types in SQL Server

User-defined data types are based on the system data types in Microsoft® SQL Server™ 2000. User-defined data types can be used when several tables must store the same type of data in a column and you must ensure that these columns have exactly the same data type, length, and nullability. For example, a user-defined data type called postal_code could be created based on the char data type. User-defined data types are not supported in table variables.

When a user-defined data type is created, you must supply these parameters:

• Name
• System data type upon which the new data type is based
• Nullability (whether the data type allows null values)

When nullability is not explicitly defined, it will be assigned based on the ANSI null default setting for the database or connection.

Note If a user-defined data type is created in the model database, it exists in all new user-defined databases. However, if the data type is created in a user-defined database, the data type exists only in that user-defined database.

Syntax:
sp_addtype [ @typename = ] type,

[ @phystype = ] system_data_type

[ , [ @nulltype = ] 'null_type' ]

[ , [ @owner = ] 'owner_name' ]

http://msdn.microsoft.com/en-us/library/aa933121(SQL.80).aspx

http://msdn.microsoft.com/en-us/library/aa259606(SQL.80).aspx

Example:
sp_addtype doj , datetime
create table tblTest (id int,dob doj)

__________________________________________________________________
User-defined types can either be created manually using Enterprise Manager or using a system stored procedure in Transact-SQL. In Enterprise Manager, simply select the “User Defined Data Types” category for the database, right click and select New User Defined Data Type. Then just complete the dialog box that appears.

In Transact-SQL, use the sp_addtype stored procedure, e.g.

EXEC sp_addtype TTelephoneNum, 'varchar(24)', 'NOT NULL'

The user-defined type can now be used in place of any of the standard types in table and procedure definitions.

Example:

CREATE TABLE PhoneBook (EntryID int IDENTITY (1,1) NOT NULL, PhoneNum TTelephoneNum, FaxNum TTelephoneNum);

CREATE PROCEDURE PhoneBookAdd(@PhoneNum TTelephoneNum, @FaxNum TTelephoneNum)
AS
INSERT INTO PhoneBook (PhoneNum, FaxNum) VALUES (@PhoneNum, @FaxNum);
GO