RSS

User Defined DataType in SQL 2008 R2

05 Sep

User Defined Data types
In our generic work around, sometime it requires to create column which having its own definition of datatype apart from existing one, this is something where User Defined Data types (UDD) come into light.
In this article , i am going to elaborate how to create datatype which having its own rules and will act as a existing datatype.

Why UDD
If we want to have column containing phone number type data e.g. +91-9898098980 (Necessarily ‘+’ at first position and ‘-‘ at fourth, you will be in the need of new data type, hence UDD come into light.

Let’s start the thing with example

Problem

Lets take same problem of having column that have
1. ‘+’ at the start.
2. ‘-‘ at fourth place.
e.g. +91-9898098980

How to proceed

Step 1 : First of all we need to create Default and Rule for Data Type, below is the query for the same.

Step 2 : We need to create User defined data type,

  • Click DataBases –> DataBase Name (TestDB) –>Programmability –> Types –> Right Click –> New –> User Defined Data Types ; you will find page as below :


Step 3 :  You can provide detail as desired and click on OK, Your UDD is ready now, detail is below

General
Schema Describes the schema of table.
Name Name of Data Type
Data Type Type of data type
Length Length of the value e.g here in this case +91-9898098980 it is 14.
Binding
Default Browse the Default which we have created initially.
Rule Browse the rule which we have created initially.

Step 4 : Open Table Design of Same database having schema dbo, you will find new data type

Now we are done with UDD, by using PhoneNumber:varchar(14), we are able to create a column which satisfies both condition.

Author : Feel free to ask if you have any query.

Advertisements
 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: