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 :
use DatabaseName Create Type UserDefineDataTypeName from datatype NOT NULL
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 –
Create Rule RuleName as @UserDefineDataTypeName not like ruletype
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”:
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”.