User-Defined Data Type in SQL

Introduction

SQL Server provides various system data types to store data like character and numeric data, etc. You can create your own data type in SQL server as well. For example, to store age of your employee you can create age type in SQL server.

This article demonstrates how to create user-defined data types using Microsoft Visual Studio and T-SQL Command.

Getting Started

User defined data type are stored in sys.types table of SQL Server, this table stores both user defined data type as well as system data type. To differentiate the user defined data type and system data type, this table contains a column named is_user_defined. The below image shows system data types. The user defined data type can be created in two ways, using T-SQL commands and Using visual studio.

SQL User-Defined Data Type Details

Create Using SQL Server Management Studio (SSMS)

This article has used Microsoft SQL Server Management Studio 2017 (SSMS 2017) to conduct demonstration. SSMS lists out the user defined data type in object explorer under User-Defined Data Type directory. See the image below to get more details and flow the steps mentioned below to create new user defined data types.

1. Open Microsoft SQL Server Management Studio, login into database using your credential.
2. Go to object explorer, then explore your Database where you want to create your data type.
3. Again, explore the Programmability then Types and then User-Defined Data Types, here you will see the list of data types if user defined data types exist in your database.

4. Right click on User-Defined Data Types then click on New User-Defined Data Types in context Manu as shown below.
   
   
5. The new User-Defined Data Types Window will be appear and check the fields in window, details of the fields are listed below.
   
   
1. Select a schema from a list of all schemas available to the current user. The default selection is the default schema for the current user.(Mandatory)
2. Name for user defined data type (Mandatory)
3. Data type for user defined data type (Mandatory).
4. Precision is Length of user defined data type (Mandatory as per data type). Displays the length or precision of the data type as applicable. Length applies to character-based user-defined data types; Precision applies only to numeric-based user-defined data types. The label changes depending on the data type selected earlier. This box is not editable if the length or precision of the selected data type is fixed.
5. Allow NULLs Specifies whether the user-defined data type can accept NULL values. The nullability of an existing user-defined data type is not editable. (Optional).
6. Default accepts a data type which is already created(user defined data type or system defined data). Optionally select a default to bind to the user-defined data type alias.
7. Rule is optionally select a rule to bind to the user-defined data type alias.
8. StorageDisplays the maximum storage size for the user-defined data type alias. Maximum storage sizes vary, based on precision.
6. In the New User-defined Data Type dialog box, in the Schema box, type the schema to own this data type alias, or use the browse button to select the schema.
7. In the Name box, type a name for the new data type alias.
8. In the Data type box, select the data type that the new data type alias will be based on.
9. Complete the Length, Precision, and Scale boxes if appropriate for that data type.
10. Check Allow NULLs if the new data type alias can permit NULL values.
11. In the Binding area, complete the Default or Rule boxes if you want to bind a default or rule to the new data type alias. Defaults and rules cannot be created in SQL Server Management Studio. Use Transact-SQL. Example code for creating defaults and rules are available in Template Explorer.

Your new user defined data type will be created successfully, to see the newly create data type again go to User-Defined Data Type.

Create User Defined Data Types Using T-SQL Command

To create new user defined data type using T-SQL Command follow the below steps.

1. Connect to the Database Engine.
2. From the Standard bar, click New Query.
3. Copy and paste code which is applicable to you from the following example into the query window and click Execute. This example creates a data type alias based on the system-supplied int data type. The age data type alias is used for columns holding 3 digits age. The column cannot be NULL.
   

     
    CREATE TYPE Age  
    FROM int NOT NULL;  
   
   --string type-------
   CREATE TYPE datatype_name  
   FROM varchar(11) NOT NULL ; 
   
   CREATE TYPE datatype_name  
   FROM NVARCHAR (11) NOT NULL ;  
    
   
   --Numeric type-------
   CREATE TYPE datatype_name  
   FROM INT NOT NULL ; 
   
   CREATE TYPE datatype_name  
   FROM BIGINT NOT NULL ; 
   
   CREATE TYPE datatype_name  
   FROM DOUBLE (18,2) NOT NULL ;  
   
   --Boolean type-------
   CREATE TYPE datatype_name  
   FROM BOT NOT NULL; 
   
   --Date type-------
   CREATE TYPE datatype_name  
   FROM DateTime NOT NULL; 
   
   CREATE TYPE datatype_name  
   FROM Date NOT NULL; 
   
   

Related Articles

1. Restore Database to Microsoft Azure Blog
2. Backup SQL Database to the Azure Blob
3. Backup SQL Database Using SSMS
4. Enable SQL Remote Connection
5. Create Encrypted SQL Database Backup

Thanks