Home > Articles > Data > SQL Server

  • Print
  • + Share This
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

    1. Expand the console tree to the database in which you want the new type.

    2. Right click on User-Defined Types and select New User-Defined Data Type.

    3. Enter the desired values.

  • Drop, Copy, Rename, Properties or Generate Script

    1. Expand the console tree to the database and User Defined Types.

    2. 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


Type_name        Storage_type        Length       Prec       Scale      Nullable        ...
------------     ----------------    --------     -----      ------     -----------     ...
empidtype        decimal             5            4          0          yes             ...

SQL

EXEC  sp_help   emps2
                  -- Partial listing to show the new data type for
                     graphics/ccc.gif empid column
                  
 

Result

Column_name            Type              ...
--------------------   -------------     ...
empid                  empidtype         ...
ename                  varchar           ...

  • + Share This
  • 🔖 Save To Your Account