MS SQL Basics Tutorial: Customizing data types

In addition to using system-supplied data types, users can also define a table's columns or declare variables as needed with a custom data type.

7.3.1 Create user-defined data types with Enterprise Manager

The way to create a user-defined data type with Enterprise Manager is to select the database in Enterprise Manager to create a user-defined type, right-click on the database object "user Defined data Types", Selecting the "New User Defined data type" option from the Start menu will show you the define user-defined Data Types Properties dialog box as shown in Figure 7-3. You can specify the name of the data type that you want to define, the inherited system data type, and whether null values are allowed. Click the OK button to add a user-defined data type object to the database.

7.3.2 Create user-defined data types using system stored procedures sp_addtype

system stored procedures provide a way for users to create custom data types in a command way. The syntax is as follows:

sp_addtype [@typename =] type,

[@phystype =] System_data_type

[, [@nulltype =] ' null_type ']

[, [@owner =] ' owner_name ']

The parameters are described as follows:

[@typename =] Type

Specifies the name of the user-defined data type. [@phystype =] System_data_type

Specifies the name and definition of the corresponding system-supplied data type. The timestamp data type cannot be used. When an additional description of the system data type is used, enclose it in quotation marks, such as: ' CHAR (8) '. [@nulltype =] ' Null_type '

Specifies the Null property of a user-defined data type whose value can be ' null ' not NULL ' or ' nonull '. The default is the same as the system default Null property. [@owner =] ' Owner_name ']

Specifies the owner of the user-defined data type.

The name of a user-defined data type should be unique in the database, but user-defined data types of different names can have the same type definition. When you use the Create TABLE command, the NULL property of a user-defined data type can be changed, but its length definition cannot be changed. Example 7-9: Define the Birthday data type.

EXEC sp_addtype birthday, datetime, ' NOT NULL '

The results of the operation are as follows:

(1 row (s) affected)

Type added.

Example 7-10: Define the ID Number data type.

exec sp_addtype cardid, ' char ', ' NOT NULL '

The results of the operation are as follows:

(1 row (s) affected)

Type added.

Example 7-11: Define the Address data type.

EXEC sp_addtype address, ' varchar ', ' NOT NULL '

The results of the operation are as follows:

(1 row (s) affected)

Type added.

7.3.3 Delete user custom data types

After you select a user-defined type in Enterprise Manager, delete it from the shortcut menu by selecting the Delete option, or you can use the system stored procedure sp_droptype to remove it. The syntax is as follows:

Sp_droptype [@typename =] ' type '

Example 7-12:

EXEC sp_droptype Cardid

The results of the operation are as follows:

(1 row (s) affected)

(0 row (s) affected)

Type has been dropped.

Note: If you delete a user-defined data type that is used by a table or another database, it will be rejected by the system.

See the full set of "MS SQL Basics Tutorials"