User-Defined Data Types (UDDT)

What is a User-Defined Data Type?

A user-defined data type provides a convenient use of underlying native datatypes for columns known to have the same domain of possible values.

Example:
EXEC sp_addtype phone_number, 'VARCHAR(20)','NOT NULL'
CREATE TABLE Customer(cust_id smallint, cust_phone phone_number)

How to create User-Defined Data Types?

User-defined data types are based on the system data types in Microsoft SQL Server. User-defined data types can be used when several tables must store the same type of data in a column and you must ensure that these columns have exactly the same data type, length, and NULLability. For example, a user-defined data type called postal_code could be created based on the char data type. User-defined data types are not supported in TABLE variables.

When a user-defined data type is created, you must supply these parameters:

Name
System data type upon which the new data type is based
NULLability (whether the data type allows NULL values)

When NULLability is not explicitly defined, it will be assigned based on the ANSI NULL default setting for the database or connection.

Note: If a user-defined data type is created in the model database, it exists in all new user-defined databases. However, if the data type is created in a user-defined database, the data type exists only in that user-defined database.

Examples:

The SQL Server user defined data types can be created both with SQL Server Management Studio and T-SQL commands. Let's walk through samples of each option to serve as an example of SQL Server user defined data types can be used with defaults and rules.

1.Creating SQL Server User Defined Data Types in SQL Server Management Studio (SSMS)
	> Open SQL Server Management Studio. > Navigate to the Databases \| AdventureWorks \| Programmability \| Types folder. > Right click on the Types folder and select New \| User-Defined Data Type
2.Syntax for creating SQL Server user defined data type
	sp_addtype [ @typename = ] type, [ @phystype = ] system_data_type [ , [ @NULLtype = ] 'NULL_type' ] [ , [ @owner = ] 'owner_name' ]

Here is a basic explanation of the four parameters from the system stored procedure:

Parameter Explanation @typename Name of new user defined data type that is being created @phystype Base system data type of SQL Server @NULLtype To specify that NULL values are allowed for this data type or not @owner Owner of this being created user defined data type

Below is the example default and rule code used in SSMS above: Example

Example Default and Rule Code
1.CREATE a default value for phone number to be used in example

USE AdventureWorks
GO

CREATE DEFAULT Default_PhNo
AS 'UnknownNumber'
GO

2.CREATE a rule for phone number to be used in example
Number will be in format +92-3335409953 or UnknownNumber by default

USE AdventureWorks
GO

CREATE RULE rule_PhNo AS (@phone='UnknownNumber')
OR (LEN(@phone)=14
AND SUBSTRING(@phone,1,1)= '+'
AND SUBSTRING(@phone,4,1)= '-')
GO

In the final code snippet, we will bind the rules and defaults to the user defined data types:

Commands to bind the defaults and rules to user defined data types

To bind a default 'Default_PhNo' to user defined data type 'PhoneNumb'

EXEC sp_bindefault 'Default_PhNo', 'PhoneNumb'

To bind a rule 'rule_PhNo' to user defined data type 'PhoneNumb'

EXEC sp_bindrule 'rule_PhNo', 'PhoneNumb'

Mr. Mansoor Ali Mohammed - Software Engineer

I love to write articles. Programming languages fascinate me and I feel at least reasonably familiar with Sql Server database and .Net Technologies. The idea of articles come from my own experiences while working in those areas and i like to share my knowledge on database with all others so that it might be helpful.

http://www.jaan1762.blogspot.com