Create User Defined Data Types In SQL Server

SQL Server has many datatypes. From geographic values to Integers, Doubles to Strings and many more. These data types help storing 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.

Creating an User-Defined Data Type using T-SQL

1. Launch SQL Server Management Studio.
2. Connect to your Server.
3. Click "New Query" in menu items.
4. Make sure you select the ideal database in 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.

Creating 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 creation of data type:
   
   
   
   
6. When you're done filling, click OK and this will create your User-Defined Data Type in the left panel.
   
   

That's it! 

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 advantage over builtin.But if you create rules and bind them with the custom data type you've recently created,im sure It will meet your needs.

Lets 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 'A' letter.Entering an input starting with 'A' will throw an error indicating that the input and rule conflicts.

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

sp_bindrule CountryCode_Rule,'CountryCode'

Executing this statement will now bind the rule to 'CountryCode' custom data type.And you won't be able to enter any input the rule doesnt 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:

• Copy Data From One Instance To Another Instance In SQL Server
• How To Generate Database Scripts In SQL Server