Articles → SQL SERVER → User Defined Data Types In SQL Server

User Defined Data Types In SQL Server

In this article, we will discuss user-defined data types in the SQL server.

Scenario Of Using The User Defined Data Types

Consider a scenario where you want to store the city name (or the first name or the last name) in the database column.

The permissible value in the column should be all alphabets i.e., no space, numbers, or special characters are allowed. So, we create a user-defined data type where users can enter only alphabets.

In this example, we will work on the following steps: -

Create a rule
Create the user-defined data type
Create a table

Create A Rule

First, create a rule that allows only alphabets.

create rule [dbo].[City_rule] 
  as 
  @value NOT LIKE '%[^a-z]%' 
and len(@value) > 0

Create The User Defined Data Type

Create a user-defined data type using the following command.

CREATE TYPE [dbo].[citytype] FROM [varchar](20) NOT NULL

Once the user-defined data type is created, right-click on the type and click on "Properties".

Picture showing the Properties option in the context menu of the user defined type

Click to Enlarge

A window will appear. You can specify the rule in the "Properties" window.

Picture showing a window to apply rule on the user defined data type in sql server

Click to Enlarge

Create A Table

Create the table with the following schema.

Picture showing adding a column in the table of user defined data type

Click to Enlarge

Now, execute the following insert query.

Insert into myTab values('Delhi')
Insert into myTab values('Delhi6')

Output

Picture showing the error message when user tries to enter the numeric value in the column

Click to Enlarge

Posted By -	Karan Gupta

Posted On -	Thursday, June 10, 2021

Query/Feedback

Your Email Id		**

Subject		*

Query/Feedback	Characters remaining 250	**