SQL Server User Defined Data Types, Rules and Defaults

By: Atif Shehzad | Read Comments (2) | Print Atif is a passionate SQL Server DBA, technical reviewer and article author. Related Tips: More

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:

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

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:

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:

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:

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

In the final code snippet, we will bind the rules and defaults to the 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.

Share:

Share

Tweet

Free SQL Server Learning Building Blocks for Your SQL Server Career Database development best practices What Are You Waiting For?

Comments and Feedback:

Monday, January 30, 2012 - 5:32:55 AM - barun kumar
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.

Monday, January 30, 2012 - 7:24:00 AM - Atif

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

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code