SQL Server platform comes with many data types, but there are cases when they are not sufficient. Or, custom data types could help make code cleaner and more transparent, and maintainable.
Custom data types are, in SQL Server wording, user-defined types (UDT). They are internally implemented as .NET objects requiring .NET Framework to be installed on SQL Server machines.
Creating and Retrieving Details about User-Defined Types
To create a user-defined data type, you have to use the existing data type as the basis for customization.
A simple example that creates user-defined type MyCustomType is below:
USE AdventureWorks2019; CREATE TYPE MyCustomType FROM NVARCHAR(255) NOT NULL;
If you later wish to see the details on user-defined types, you can query the sys.types system table:
SELECT * FROM sys.types WHERE name = 'MyCustomType';
If you wish to get all UDTs in your server, you can query by field is_user_defined:
SELECT * FROM sys.types WHERE is_user_defined = 1
AdventureWorks already contains some user-defined types, and the one we just created is listed last.
Using User-Defined Data Types
For the user-defined types, you need to create a table with that column type in one of its definitions, as shown in the example below:
— First, we create a table with this UDT
CREATE TABLE CustomTest ( UDTTest MyCustomType PRIMARY KEY, TextValue NVARCHAR(255), NumValue INT );
— Then we can take a look at system tables to find out what we can do with table properties
SELECT SysCols.name , SysCols.system_type_id , SysCols.user_type_id , SysCols.collation_name , SysCols.is_identity , SysCols.is_nullable , SysCols.max_length --, SysCols.* FROM sys.columns SysCols INNER JOIN sys.tables SysTables ON SysTables.object_id = SysCols.object_id WHERE SysTables.name = 'CustomTest';
Inserting and Retrieving Data from Table with a User-Defined Type
We start by inserting a couple of lines into the created table:
INSERT INTO CustomTest(UDTTest, TextValue, NumValue ) VALUES('0001', 'Test1', '1'), ('0002', 'Test2', '2'), ('0003', 'Test3', '3'), ('0004', 'Test4', '4');
— Data is accepted and retrieved
SELECT * FROM CustomTest
Now we try to insert a record that doesn’t fit the specified data type definition. In this case, breaking NOT NULL property of PRIMARY KEY:
INSERT INTO CustomTest(UDTTest, TextValue, NumValue ) VALUES( NULL, 'Test5', '5');
You define a custom data type, and then you use it like any other basic data type, respecting limitations in its definition.
To create user-defined types a user needs to have the CREATE TYPE permission.
User-defined data types cannot be modified. They have to be dropped using the DROP TYPE command and recreated using the CREATE TYPE command.
User-defined data types can prove very useful in scenarios where you need to achieve consistency on the custom formatted data. It is especially handy when they are used as a type of data quality checks or allows conformed data types over multiple layers in data warehouse data transfers.Last modified: March 18, 2022