Creating a new User-Defined Data Type–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I ran across a question on user-defined data types, which I hadn’t worked with in a long time, so I took a minute to investigate. I wrote this post about an interesting language item.

User-defined table types let you add a new type that can be used anywhere you would use a base, or normal, data type. This means if I want to have a type of US zip codes, perhaps limiting the base zip to 5 numbers, I can create a type that is limited to 5 digits.

If I want to create a new user-defined table type, I’d have thought I did something like this:


However that doesn’t work.

2022-01-06 10_20_55-SQLQuery1.sql - ARISTOTLE.sandbox (ARISTOTLE_Steve (58))_ - Microsoft SQL Server

The AS structure is used in many places, but not here. Instead, we use a FROM structure. This means I’d do this:


This gives me a type I can use in CREATE TABLE statements, stored procedures, and more. Anywhere I’d use the varchar(5), I could do this instead:

CREATE TABLE dbo.AddressTable
, AddressValue VARCHAR(100)
, AddressZip USZipCode

This let’s me use a type that is more intuitive, I guess. I don’t find these that useful in most places. In fact, it’s a little confusing. If I were a new developer, is this a 5 or 9 (or 10) digit field? Is it numeric or string? It’s not easy to determine this. I don’t find these that useful.

SQL New Blogger

I was doing other work, but I saved a bit of code and then spent about 10-15 minutes to write up this post. This one shows less about what I learned, and more about what I think.

Always good to show to a prospective interviewer.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.

2 Responses to Creating a new User-Defined Data Type–#SQLNewBlogger

  1. aaronbertrand says:

    And just wait until you have to make it zip + 5 but `dbo.USZipCode` is embedded in your tables and modules.


Comments are closed.