SQL Server Custom Data type: A How-To Guide

One of the more interesting things you can do within Microsoft SQL Server is create your own custom data type.

The process is actually very simple. In this very brief tutorial, we’re going to learn how to create a custom data type in SQL Server. We’ll discuss these topics:

1. The idea behind a custom data type in SQL Server
2. The CREATE TYPE statement
3. Examples of custom data types
4. Dropping a custom data type
5. Tips and tricks

Also, all the content from this tutorial can be found in the following FREE guide:

FREE EBook on SQL Server Data Types!

Definitely download that guide if you want to learn about the most common data types you will encounter as a database professional.

Let’s start from the top:

1. The idea behind a custom data type in SQL Server

The first thing we need to establish is the idea behind a custom data type, which are actually called “user-defined data types” in Microsoft SQL Server.

Understand that we aren’t really creating a brand new data type. What we’ll do is actually closer to creating an alias for an already-existing data type.

If you come from a programming background, creating a brand new data type is essentially what you do when you create a class. But again, we’re not doing anything like that in SQL Server. What we’ll do is actually similar to using “typedef“, which is something you might be familiar with if you have worked with any of the C programming languages before.

But rest assured. If you’re still lost, it’s ok. I’m going to break it down and show you what I mean.

2. The CREATE TYPE statement

To create a custom data type in SQL Server we use the handy CREATE TYPE statement. The syntax is as follows:

CREATE TYPE <name_of_your_type> FROM <base_type> <nullability>

For example, if we wanted to create a custom data type called ‘”Spagetti” that is based off the DECIMAL(5,2) data type, and have it disallow NULL, the statement would look like this:

CREATE TYPE Spagetti FROM DECIMAL(5,2) NOT NULL

Now, anytime we want to use DECIMAL(5,2) in our database, we can instead use Spagetti! For example, check out this query:

Get it? Again, we’re basically creating an alias for DECIMAL(5,2). Having to type out DECIMAL(5,2) all the time might get tiring after a while. Or maybe we want to make sure all decimal types in our database are all the same, so we have a business rule we all agree to that says: “If you want to store a decimal value, don’t use DECIMAL at all. Instead, use our custom Spagetti data type. That way, we are all consistent.”

3. Examples of custom data types

Ok, let’s look at a couple of tables. Let’s say we have the following Employees table:

CREATE TABLE Employees
(
EmplID INT PRIMARY KEY IDENTITY,
FirstName VARCHAR(20),
MiddleName VARCHAR(20),
LastName VARCHAR(20)
)

Then we have the following Customers table:

CREATE TABLE Customers
(
CustID INT IDENTITY(10,5),
FirstName VARCHAR(20),
MiddleName VARCHAR(20),
LastName VARCHAR(20),
StateCode CHAR(2),
City VARCHAR(15)
)

Do you see a data type that is repeated several times?

I do. It’s VARCHAR(20).

We use this data type any time we want to store a name value, like a first name, middle name, or a last name.

Maybe we want to make things easier and create a custom data type that allows us to specify it rather than VARCHAR(20) all the time.

And again, we’ll create a business rule in our organization saying if we need to store a name value, we should use the custom data type. That way, we know they will all essentially be VARCHAR(20).

So we could create a custom data type called NameType that can be used in place of VARCHAR(20). We’ll say this type allows NULL. Here’s the syntax:

CREATE TYPE NameType FROM VARCHAR(20) NULL

So now, we could write our CREATE TABLE statements like this instead:

CREATE TABLE Employees
(
EmplID INT PRIMARY KEY IDENTITY,
FirstName NameType,
MiddleName NameType,
LastName NameType
)

CREATE TABLE Customers
(
CustID INT IDENTITY(10,5),
FirstName NameType,
MiddleName NameType,
LastName NameType,
StateCode CHAR(2),
City VARCHAR(15)
)

Again, now we can be assured that any name value in our database will basically be VARCHAR(20) behind the scenes. In SQL Server, it’s nice when you can rely on things. Our new data type can be used anywhere, like when defining columns as I’ve demonstrated, or in a stored procedure, or in a function, whatever!

4. Dropping a custom data type

It’s easy peasy: DROP TYPE <type_name>

Here’s an example:

DROP TYPE NameType

5. Tips and tricks

Here is a list of a few tips and tricks you should know when working with a custom data type.

Tip # 1. You can leave off the nullability value and the type will default to allow NULL

Earlier, we wanted our custom NameType data type to be nullable. We created the type like this:

CREATE TYPE NameType FROM VARCHAR(20) NULL

We could have simply left off the “NULL” keyword and the type would have defaulted to allow NULL anyway:

CREATE TYPE NameType FROM VARCHAR(20)

If you leave off the nullability, the type will default to allow NULL.

Tip # 2. You can see the custom data types in your database by looking in the Object Explorer

In your object explorer, expand your database and navigate to Programmability | Types | User-Defined Data Types. Like so:

Next Steps:

Leave a comment if you found this tutorial helpful!

Everything discussed in this tutorial can be found in the following FREE Ebook:

FREE EBook on SQL Server Data Types!

In this guide, we discuss several data types you should know as a database professional. Make sure you download it today!

This tutorial assumes you know a thing or two about data types in SQL Server. If you want a crash course on several data types we can use in SQL Server, check out these tutorials:

Character String data types:

SQL Server character data types (and the differences between them)

Integer data types:

SQL Integer Data Types: Everything you need to know

Decimal data type:

SQL Server DECIMAL: Everything you need to know

Do you know the difference between DECIMAL and NUMERIC?:

SQL DECIMAL vs NUMERIC: What’s the difference? (THERE IS NONE)

Thank you very much for reading!

Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!

If you have any questions, or if you are struggling with a different topic related to SQL Server, I’d be happy to discuss it. Leave a comment or visit my contact page and send me an email!