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.