Creating a new User-Defined Data Type–#SQLNewBlogger

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:

CREATE TYPE dbo.USZipCode AS VARCHAR(5)

However that doesn’t work.

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

CREATE TYPE dbo.USZipCode FROM VARCHAR(5)

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

( AddressID INT NOT NULL

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