This chapter is from the book
This chapter is from the book
This chapter is from the book
2.5 USER-DEFINED DATA TYPES
User-defined data types are data types based on intrinsic system types given a name by the user, which may then be used in future DDL statements within the database where they were created.
It is particularly useful to create a user-defined data type for a unique key that has foreign key columns (usually in another table) that refer to it. Creating a user-defined type and using it for the unique key and foreign key column definitions ensures that they are the same data type.
The following parts of user-defined data types must be provided when created.
-
Name.
-
Underlying System Data Type— see Table 2-50.
-
Nullability— 'NULL', 'NOT NULL', 'NONULL' in single quotes, see below. For a discussion of default nullability see page 230.
2.5.1 Enterprise Manager—Create and Manage a User-Defined Data Type
-
Create
-
Expand the console tree to the database in which you want the new type.
-
Right click on User-Defined Types and select New User-Defined Data Type.
-
Enter the desired values.
-
-
Drop, Copy, Rename, Properties or Generate Script
-
Expand the console tree to the database and User Defined Types.
-
Right click on the user-defined data type and select the operation desired.
-
2.5.2 Transact-SQL—Create and Manage a User-Defined Data Type
Four phrases can be used to create and manage a user-defined data type:
-
sp_addtype: Creates a user-defined data type in the current database.
-
sp_droptype: Deletes a user-defined data type from the current database.
-
sp_rename: Changes the name of the user-created object.
-
sp_help <typename>: Displays the definition of the user-defined data type (or system data type).
sp_addtype
The addtype phrase creates a user-defined data type in the current database.
Syntax
sp_addtype [ @typename = ] type,
[ @phystype = ] system_data_type
[ , [ @nulltype = ]{'NULL' | 'NOT NULL' | 'NONULL' } ]
[ , [ @owner = ] 'owner_name' ] -- defaults to current user
Arguments
| [@typename =] type |
This is the name of the user-defined data type to be created; it must be unique in the database. |
| [@phystype =] system_data_type |
This is the physical SQL Server data type being defined as the underlying or base data type. It has no default and must be one of the values given in Table 2-50. |
Table 2-50. Base Data Type Values
|
'BINARY( n )' |
IMAGE |
SMALLDATETIME |
|
BIT |
INT |
SMALLINT |
|
'CHAR( n )' |
'NCHAR( n )' |
TEXT |
|
DATETIME |
NTEXT |
TINYINT |
|
DECIMAL |
NUMERIC |
UNIQUEIDENTIFIER |
|
'DECIMAL[ ( p [, s ] ) ]' |
'NUMERIC[ ( p [ , s ] ) ]' |
'VARBINARY( n )' |
|
FLOAT |
'NVARCHAR( n )' |
'VARCHAR( n )' |
|
'FLOAT( n )' |
REAL |
|
|
Quotation marks are required if there are embedded blank spaces or punctuation marks including parenthesis, (), or square brackets, []. |
||
[ , [ @nulltype = ] {'NULL' | 'NOT NULL' | 'NONULL' } ]
If not specified in sp_addtype the nullability is set to the current default nullability for the database as can be seen with the GETANSINULL() system function and which can be changed using SET or ALTER DATABASE.
I suggest that nullability be explicitly specified in sp_addtype. If specified in sp_addtype, the setting becomes the default nullability for the user-defined data type but can be set to a different value as with CREATE TABLE or ALTER TABLE.
For a discussion of nullability see page 230.
[@owner =] 'owner_name'
This specifies the owner or creator of the new data type. When not specified, owner_name is the current user.
Return Code Values
0 (success) or 1 (failure)
Comments
Note that the main features that can be set in a user-defined data type are the
-
base data type
-
size
-
nullability
-
owner
Constraints to limit permissible values cannot be assigned as in the ANSI SQL notion of domain. Nonetheless, user-defined data type is a useful concept to improve readability and consistency among related tables using foreign keys.
Permissions
Execute permissions default to the public role.
sp_droptype
The droptype phrase deletes a user-defined data type from the current database.
Syntax
sp_droptype [ @typename = ] 'type'
Arguments
[@typename =] type
This is the name of the user-defined data type to be dropped.
sp_rename
The rename function changes the name of the user-created object.
Syntax
sp_rename [ @objname = ] 'object_name' -- Current name of the type to be renamed , [ @newname = ] 'new_name' -- New name of the type [ , [ @objtype = ] 'object_type' ] -- USERDATATYPE for a user-defined data type sp_rename [ @objname = ] 'object_name' , [ @newname = ] 'new_name' [ , [ @objtype = ] 'object_type' ] -- USERDATATYPE for a user-defined data type
Example:
|
SQL |
|---|
EXEC sp_addtype empidtype , 'DEC(4)' , 'NULL'
CREATE TABLE emps2 (
empid empidtype PRIMARY KEY, -- makes it NOT NULL
ename VARCHAR(20)
)
INSERT INTO emps2 VALUES ( 1111 , 'James Bond' )
EXEC sp_help empidtype
|
|
Result |
|
|
|
SQL |
|---|
EXEC sp_help emps2
-- Partial listing to show the new data type for
|
|
Result |
|
Column_name Type ... |
empid column