This feature was introduced in Microsoft SQL Server 2008. This is basically for reusability.

Suppose you maintain a CreatedDate field in every table for log purposes. In that case, every time you need to set the datatype as DATETIME then set NOT NULL constraint and Default value=getdate(). It creates extra work and also there are chances of inconsistency in the database schema. Because of this User Defined Types are very useful feature.

How to create User Defined Type
  1. CREATE TYPE CreatedDate
  3. FROM DATETIME NULL

1.png

You can see that this creates a type at Database -> Programmability -> Types -> User Defined Data Types.

2.png

If you want to set a default value for this type then we need to create a default for this and need to bind that default value to the type created earlier.

For Example

  1. CREATE DEFAULT DefaultDate
  3. as GETDATE()
  5. //Stored Procedure to bind default to the Type
  7. EXEC sp_bindefault 'DefaultDate', 'CreatedDate'

3.png

Now you can directly use "CreatedDate" as the type during table creation.

For Example

I have created a Student table with some basic details and CreatedDate field of type "CreatedDate".

4.png

Now, I have inserted values into the Student table by excluding CreatedDate.

You can see the result below.

5.png

Now you can use this type in any table. It will reduce your work and chances of inconsistency.

You can also define Data Types using GUI as in the following.

  • Open SQL Server Management Studio.
  • Navigate to Databases then to YourDatabase then Programmability then the Types folder.
  • Right-click on the Types folder and select New -> User-Defined Data Type as shown below:

6.png

Here in this example I am using the previously created "Default". You can create a new Default manually and can select over here.

You can use UDT for various types of fields that are repeating in tables like Name, Address and Flag. Just create it once and use many times.

Please share your thoughts about this article.

Thank you so much for your time.