Problem Statement : Recently, I was working on new a application and there I faced one challenge that led me to write an article on that problem. We had one column that we were using in multiple tables in our application database. Column name was “Effort” and its data type was “Numeric(18,2)“(not at all places) that was the challenge. We had 4-5 database developers and everybody was choosing different datatypes for “Effort” column in different tables. Somebody created “Effort” column with datatype “int” and somebody created with “numeric” and few of them created with “decimal” datatype.
Solution: Microsoft has provided a feature of creating user datatypes which can be useful to overcome this problem. Creating a user defined datatype is helpful when we talk about consistency or symmetry. By creating a user defined datatype, we could have avoid inconsistency problem in our application.
Due to inconsistency of datatypes, we had to do lot of rework. So I thought to share this experience with my blog readers.
Syntax of creating User Defined Datatype: Using below SQL statement, we can create user defined datatype.
Create type Effort from Numeric(18,2)
Lets create two tables using data type “Effort”
Create table WorkOrder(WorkOrderId int,EstimatedEffort Effort)
Create table JobWork(JobOrderId int,EstimatedEffort Effort)
In the above two create table script, instead of specifying system datatype, we have specified User defined datatype “Effort” , After creating the user defined data type, we can ask our database developers just to use “Effort” datatype wherever we are creating a column related to effort. In that way, we can make sure the consistency of datatypes.
Note: Before defining the user defined datatype, we have to make sure that we have chosen the correct length of the datatype otherwise you will not be able to change the length of datatype in later stage.
Please feel free to add your comments , if you have any other views on this topic.
1,685 total views, 4 views today