RSS

Category Archives: User Defined DataType in SQL 2008 R2

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.

Advertisements