User-Defined Data Types In SQL Server

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.

How to Create User-Defined Data Types (UDT)

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:

USE AdventureWorks2019;

— 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.

Conclusion

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.