Create a User-Defined Data Type Alias in SQL Server

SQL Server has multiple data types e.g. integers, char, varchar, doubles, strings, etc. which are used for keeping specific values. Although built-in data types could store specific values, sometimes SQL DBA may need to store more specific values and create customized data types.

We can create a user-defined data type using below two methods –

• Using T-SQL
• Using SQL Server Management Studio

Create a User-Defined Data Type using T-SQL :

Syntax –

use DatabaseName
Create Type UserDefineDataTypeName    
from datatype NOT NULL

Example –
Creating a User-Defined Data Type named “Code” which stores only 4 letters.

use Database name
Create Type Code    
from varchar(4) NOT NULL

Create a User-Defined Data Type using SQL Server Management Studio :

• Connect to SQL Server
• Expand Database
• Expand Programmability

• Expand Types
• Select User Defined Data Types
• Right-click on it and select “New User-Defined Data Type“

Fill in the required information to create data type, Click OK.

Use custom datatype in a table or SQL query –

Note: User-Defined Data Types standalone does not have an advantage over built-in. But if rules are created to bind them with the custom data type you have recently created.

1. Create a rule –

Syntax –

Create Rule RuleName  
as @UserDefineDataTypeName not like ruletype

Example –

Create Rule Code_Rule  
as @Code not like '%D%'

In the above example, a rule that accepts every input except word with ‘D’ letter. 

2. Bind the Rule and User-Defined Data Type –
To combine or bind the Rule and User-Defined Data Type: we could use the stored procedure “sp_bindrule”:

Syntax –

sp_bindrule RuleName,'UserDefineDataTypeName'

Example –

sp_bindrule Code_Rule,'Code'

In the above example, Code_Rule will bind the rule to user-defined data type ‘Code’.

3. Unbind the rule from User-Defined Data Type –

To unbind the rule from User-Defined Data Type use “sp_unbindrule”.

Syntax –

sp_unbindrule 'UserDefineDataTypeName'

Example –

sp_unbindrule 'Code'