Creating User-defined Datatypes

You create user-defined datatypes based on SQL Server-supplied datatypes.

Note To use a user-defined datatype in more than one database, create it in the model database. The user-defined datatype definition then becomes known to all databases created after the change to the model database.

    To create a user-defined datatype

• From the Manage menu in SQL Enterprise Manager, choose User-Defined Datatypes, and then create the datatype in the Manage User-Defined Datatypes dialog box.

  (For details, search for "user defined datatypes" in SQL Enterprise Manager Help.)

Or

• Execute the sp_addtype system stored procedure.

  For example:

  sp_addtype ssn, 'varchar(11)', 'NOT NULL' 
  

  creates a user-defined datatype called ssn (social security number) that is based on the SQL Server-supplied varchar datatype. The ssn datatype will be used for columns holding 11-digit social security numbers (xxx-xx-xxxx). That column cannot be NULL.

  (For details, see the sp_addtype system stored procedure in the Microsoft SQL Server Transact-SQL Reference.)