SQL Server User Defined Data Types, Rules and Defaults

By:   |   Comments (11)   |   Related: > Database Design


Problem

SQL Server provides numerous system data types to store dates, character based data, numeric data, etc.  However there are some situations when a customized data type is needed for consistency across an application. Some examples could be phone numbers in a specific format, alpha numeric employee ID's, IP addresses, etc.  What options are available to store this data in a unique manner?

Solution

Although a few different design options are available, one option is to create SQL Server user defined data types and bind them to an existing default and rule.  In some respects, they can be considered customized SQL Server data types.  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.

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.. as shown below:

SSMS UserDefinedDataType 1

General page of User-Defined Data Type

Below is a example view of the General page for a user defined data type creation.

  • Default schema for user defined data type as 'dbo'
  • Name for user defined data type as 'PhoneNumb'
  • Data type for user defined data type as 'varchar'
  • Length of user defined data type as 14
  • Allow NULLs is unchecked, so NULL will not be allowed for this data type.
  • As a default I have browsed a pre created default 'dbo.Default_phNo' which is shown in the T-SQL section below
  • As a rule for this user defined data type I have browsed a pre created rule 'dbo.rule_PhNo' which is shown in the T-SQL section below

SSMS UserDefinedDataType 2

Extended Properties page of User-Defined Data Type

I have added two extended properties for the User-Defined Data Type which can be considered meta data.  In this example I have included the author (Created By) and purpose (Created For) as shown below:

SSMS UserDefinedDataType 3

T-SQL Implementation of the User Defined Data Types, Rules and Defaults

Below outlines the sample code for the User Defined Data Types, Rules and Defaults:

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 Default and Rule Code

-- Create a default value for phone number to be used in example
USE AdventureWorks
GO

CREATE DEFAULT Default_PhNo
AS 'UnknownNumber'
GO

-- 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'
GO
-- To bind a rule 'rule_PhNo' to user defined data type 'PhoneNumb'
EXEC sp_bindrule 'rule_PhNo', 'PhoneNumb'
GO
Next Steps
  • Creating and using your own defined data types with rules and defaults in SQL Server will provide a powerful mechanism to implement uniformity and consistency in your database design.
  • Keep in mind that a user defined data type in SQL Server is only accessible in a single database as such, it could make sense to script out, create the objects in dependent databases and update the database design.
  • One trick with new databases is to create the objects in the model database, so as new databases are created the user defined data types, rules, defaults, etc. will automatically be available.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Atif Shehzad Atif Shehzad is a passionate SQL Server DBA, technical reviewer and article author.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Tuesday, July 18, 2017 - 12:01:02 AM - MAHADEVAN ANNAMALAI Back To Top (59516)

 Thanks Atif Shehzad...

this post very helpful.

 

 

Monday, May 2, 2016 - 2:43:56 AM - Prav Back To Top (41377)

 

 Thank you so much..

 

 

Friday, April 17, 2015 - 4:41:25 PM - Igor Back To Top (36968)

Very interesting post. Thanks.

Wednesday, January 15, 2014 - 9:24:17 AM - Lonyem Back To Top (28090)

Atif, thank you, sir!

This post did come in handy for me. Much appreciated.

Monday, July 8, 2013 - 6:48:33 AM - Atif Shehzad Back To Top (25743)

@Fred. Please follow the steps provided in followig article

http://blog.netnerds.net/2008/12/sql-server-creating-a-user-defined-data-type-from-scratch/

 

Thanks

Saturday, July 6, 2013 - 5:23:24 AM - Fred Back To Top (25733)

Please i want you to write me a code for a user defined data type for an IP Address

Tuesday, September 18, 2012 - 2:53:32 AM - Atif Shehzad Back To Top (19544)

@Vikas. User defined type may not be altered however you may bind/unbind defaults, rules with it at any time. To change a UDT you have to drop and recreate it.

Thanks

 

Saturday, July 14, 2012 - 3:24:20 AM - vikas kumar pathak Back To Top (18514)

How we can see  an dalter the structure of the user define type? is There any way using sql script

 

Tuesday, May 22, 2012 - 6:32:08 AM - Ruchir Back To Top (17584)

Gr8 Post...

Monday, January 30, 2012 - 7:24:00 AM - Atif Back To Top (15826)

Hi Barun, The variable @phone is being used while creating rule in the article. According to general rules it is unlogical to use a variable without first declaring it. But while creating a rule in SQL Server we may use one variable without declaration. However it should be prefixed by @ sign. It directly refers to updated or inserted value for which rule is being applied. So there is exception in case of creating SQL Server rule and you may use a variable in rule without declraing it.

Please also take in account that this feature may not be available in coming versions of SQL Server. According to BOL

"This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. We recommend that you use check constraints instead. Check constraints are created by using the CHECK keyword of CREATE TABLE or ALTER TABLE."

 

Monday, January 30, 2012 - 5:32:55 AM - barun kumar Back To Top (15825)

Hello sir,

i have read your blogs on SQL Server User Defined Data Types, Rules and Defaults . It's very useful for me but i have a doubt that you use a variable @phone while creating the rule without declaring it and it work properly without generating any error why?

Please clear my doubt.














get free sql tips
agree to terms