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 a user-defined data type with Enterprise Manager
to create a user-defined data type using Enterprise Manager, 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 Use system stored procedures sp_addtype to create user-defined data types
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: Defines a birthday data type.
exec sp_addtype birthday, datetime, ' NOT NULL '
The results of
operation are as follows:
(1 row (s) affected)
Type added.
Example 7-10: Defines the ID Number data type.
exec sp_addtype cardid, ' char ', ' NOT NULL '
The results of
operation are as follows:
(1 row (s) affected)
Type added.
Example 7-11: Defines the address data type.
exec sp_addtype address, ' varchar ', ' NOT NULL '
The results of
operation are as follows:
(1 row (s) affected)
Type added.
7.3.3 Delete user-defined data types
You can select a user-defined type in Enterprise Manager, select Delete from the shortcut menu to choose
The
item deletes it, or it can be deleted using a system stored procedure sp_droptype. The syntax is as follows:
Sp_droptype [@typename =] ' type '
Case 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.
