Post
Ask Question

Create User Defined Data Types In SQL Server

Introduction

SQL Server has many data types. From geographic values to Integers, Doubles to Strings, and many more. These data types help store specific values.

Even though built-in data types can store specific values, sometimes you may need to create customized data types.

Speaking of it, I'd like to create a user-defined data type using 2 methods: 

  1. T-SQL
  2. Manually

In our example, I'll be creating a UDDT named "CountryCode" which stores only 3 letters of the country.

How to Create a User-Defined Data Type using T-SQL?

  1. Launch SQL Server Management Studio.
  2. Connect to your Server.
  3. Click "New Query" in the menu items.
  4. Make sure you select the ideal database which you want to create.
  5. Write these TSQL codes: 
    Create Type CountryCode    
from varchar(3) NOT NULL    

  6.  Execute

  7. The UDDT will be created under {Database}-Programmability-Types-User Defined Data Types section.

How to Create a User-Defined Data Type Manually?

  1. Launch SQL Server Management Studio.
  2. Connect to your Server.
  3. Follow path {Database}-Programmability-Types-User Defined Data Types section and right-click on it.
  4. Choose "New User-Defined Data Type."

  5. Fill in specific information regarding the creation of data type:

  6. When you're done filling in, click OK and this will create your User-Defined Data Type in the left panel.

Now you can use your custom datatype in a table or SQL query.

UPDATE

As per feedback, I'd like to give a much more useful tip on

where to use User Defined Data Types?

User Defined Data Types standalone does not have an advantage over built-in. But if you create rules and bind them with the custom data type you've recently created, I'm sure It will meet your needs.

Let's create a rule

Create Rule CountryCode_Rule  
as @CountryCode not like 'A%'  

In the example code above, I've created a rule that accepts every input except starting with the 'A' letter. Entering an input starting with 'A' will throw an error indicating that the input and rule conflict.

But our work hasn't finished yet. We need to bind the Rule and User Defined Data Type. For this we use a stored procedure named "sp_bindrule":

sp_bindrule CountryCode_Rule,'CountryCode'  

Executing this statement will now bind the rule to the 'CountryCode' custom data type. And you won't be able to enter any input the rule doesn't accept.

However, If you want to unbind the rule from User Defined Data Type, you need to use "sp_unbindrule" with the User Defined Data Type parameter.

sp_unbindrule 'CountryCode'  

Read more articles on SQL Server


Similar Articles