In addition to the many data types available in SQL Server, you also have the option of creating your own data type. Some of these are referred to as “user-defined data types”, while others are referred to as “alias data types”.

A user-defined data type is implemented through a class of an assembly in the Microsoft.NET Framework common language runtime (CLR).

An alias data type is based on a SQL Server native system type. In other words, you use an existing data type for the basis of your alias data type.

Having said that, I’ve seen Microsoft use the term “user-defined data type alias” when referring to an alias data type. I’ve also seen it referred to as simply a “data type alias”.

Either way, this article demonstrates how to a create a user-defined data type alias using Transact-SQL.

Example 1 – Create an Alias Data Type

To create an alias data type, run a CREATE TYPE statement against the database that you want to create the alias data type.

Here’s a code example that creates a user-defined data type alias based on the SQL Server varchar data type:

USE Test;

CREATE TYPE clientcode  
FROM varchar(8) NOT NULL;

Result:

Commands completed successfully.
Total execution time: 00:00:00.028

In this case, I create an alias data type called clientcode in a database called Test.

My alias is based on varchar(8), which means that it can be a variable length string up to 8 bytes in length. For single-byte encoding character sets (such as Latin), this will store up to 8 characters. However, for multi-byte encoding character sets, the number of characters may be less.

Example 2 – View the Alias Data Type

You can use sys.types to check the details of your alias data type:

SELECT * FROM sys.types
WHERE name = 'clientcode';

Result:

name              | clientcode
system_type_id    | 167
user_type_id      | 257
schema_id         | 1
principal_id      | NULL
max_length        | 8
precision         | 0
scale             | 0
collation_name    | SQL_Latin1_General_CP1_CI_AS
is_nullable       | 0
is_user_defined   | 1
is_assembly_type  | 0
default_object_id | 0
rule_object_id    | 0
is_table_type     | 0

We can see that the is_user_defined flag for this data type is 1, meaning that it’s a user defined data type.

In this example I narrowed the results to just the clientcode data type. You can use sys.types to return information on all data types in the database. See How to Return a List of Data Types in SQL Server for more information.

Now that the alias data type has been created, we can go ahead and use it.

Example 3 – Create a Table that Uses the Alias

In this example I create a table that uses my newly created data type alias in one of its column definitions.

USE Test;

CREATE TABLE Client
(
    ClientCode clientcode PRIMARY KEY,
    FirstName varchar(50),
    LastName varchar(50)
);

We can take a quick look at the columns in the table:

SELECT 
  c.name,  
  c.system_type_id,
  c.user_type_id,
  c.max_length,
  c.is_nullable
FROM sys.columns c
INNER JOIN sys.tables t
ON t.object_id = c.object_id
WHERE t.name = 'Client';

Results:

+------------+------------------+----------------+--------------+---------------+
| name       | system_type_id   | user_type_id   | max_length   | is_nullable   |
|------------+------------------+----------------+--------------+---------------|
| ClientCode | 167              | 257            | 8            | 0             |
| FirstName  | 167              | 167            | 50           | 1             |
| LastName   | 167              | 167            | 50           | 1             |
+------------+------------------+----------------+--------------+---------------+

There are many more columns of data but I’ve narrowed it down to just some that are relevant to this article.

Example 4 – Insert Data

Now it’s time to insert data into the column that uses our user-defined data type alias.

INSERT INTO Client
VALUES ('aaa00001', 'Satoshi', 'Nakamoto');

And now select the row:

SELECT * FROM Client;

Result:

+--------------+-------------+------------+
| ClientCode   | FirstName   | LastName   |
|--------------+-------------+------------|
| aaa00001     | Satoshi     | Nakamoto   |
+--------------+-------------+------------+

So we can see that our alias data type has accepted the data as specified.

But it wouldn’t be a proper test unless we tried to break it.

Let’s try to insert a value that doesn’t adhere to our alias:

INSERT INTO Client
VALUES ('aaaa00002', 'Mikko', 'Linnamäki');

Result:

Msg 8152, Level 16, State 30, Line 1
String or binary data would be truncated.

In this case I tried to insert a value that would require 9 bytes to store, but the alias only accepts values up to 8 bytes, so it rejected it.

If I remove one of the characters, it works fine:

INSERT INTO Client
VALUES ('aaa00002', 'Mikko', 'Linnamäki');

SELECT * FROM Client;

Result:

+--------------+-------------+------------+
| ClientCode   | FirstName   | LastName   |
|--------------+-------------+------------|
| aaa00001     | Satoshi     | Nakamoto   |
| aaa00002     | Mikko       | Linnamäki  |
+--------------+-------------+------------+