Skip to content
Related Articles

Related Articles

Improve Article
Save Article
Like Article

Create a User-Defined Data Type Alias in SQL Server

  • Last Updated : 17 Mar, 2021

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
  • ight-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'
My Personal Notes arrow_drop_up
Recommended Articles
Page :

Start Your Coding Journey Now!