Open In App

Create a User-Defined Data Type Alias in SQL Server

Improve
Improve
Like Article
Like
Save
Share
Report

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'

Last Updated : 16 Feb, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads