Wednesday, 15 June 2016

User Defined DataType in SQL 2008 R2


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.

1 comment:

Anonymous said...

Nice!!!