"What's the Point of [SQL Server] User-Defined Types?"

I'm asked that question every now and then from other developers who've played around in SQL Server Enterprise Manager and noticed the "User Defined Data Types" tab under their database. UDT seem a bit strange and pointless because they do not allow one to define (as one might expect) a data structure with more than one related data element. A UDT consists simply of a name and a base type (INT, VARCHAR(6), etc).

So why then would one use a UDT? It all has to do with a fundamental concept of data known as "domains." I'm not referring to a dot-com type domain, but a domain in the mathematical sense of restricting the value of a particular value. For example, the domain of x for "f(x) = 1/x" is "!=0".

We don't get domains in C++ / C# / VB / etc; all we have are types (integer, date, string, etc). But we're used to not having this; everyone knows you need to check if "x != 0" before trying to divide by x.  Imagine how much less coding (and related bugs) we'd have if trying to assign "0" to "x" threw an exception from the start, instead of in the middle. That's exactly what you can (and should) be doing with your databases. 

When I start on this same explanation to others, it turns out a lot don't quite understand what check constraints are. Basically, check constraints are used to define the domain of a column to ensure that a row can only contain valid data according to the business rules. For example, your Products table should have a check constraint on the Price column, requiring it to be greater than zero (this would cause an exception to be raised if you tried to update the price to zero). Here's another example of some code:

CREATE TABLE [Transactions] (
  [Transaction_Id] INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
  [Transaction_Type] VARCHAR(5) NOT NULL
    CHECK ([Transaction_Type] IN ('Debit','Credit','Escrow')),
  [Transaction_Amount] DECIMAL(4,2) NOT NULL
    CHECK ([Transaction_Amount] <> 0),
  [Reference_Code] CHAR(5)
    CHECK ([Reference_Code] LIKE '[A-Z][ A-Z][A-Z][A-Z][A-Z]'))
)

Get the idea? Each column has a constraint to ensure only valid data is allowed in the table. This way, there is no way that [Reference_Code] could contain anything but a five character string of upper case letters. No need to write code to test it, no need to ever validate it (except maybe on the data entry form so that the user doesn't see an ugly exception message), and no need to assume that it will be anything but that.

Now, immagine that you wanted to have the same [Reference_Code] attribute throughout your database. You'd have to define that check constraint time and time again. If the rules ever changed, you'd need to change it in every place. That's where UDTs come into place. UDTs are the SQL Server imlementation of domains.

If you have a common data element that will be used throughout the system, then it should be a UDT. Account number, Username, Order Number, etc; all should be UDT. When you define these types, you can easily apply rules (which are essentially just check constraints that apply whenever the type is used) to the type, and have it automatically enforced throughout the system.

It's really easy to do. I'll use the SQL 2005 syntax, but you can do the same things in 2000 using sp_addtype and sp_addrule:

CREATE TYPE USERNAME FROM VARCHAR(20)
GO

CREATE RULE USERNAME_Domain
    AS @Username = LTRIM(RTRIM(@Username))
   AND LOWER(@Username) NOT IN ('admin','administrator','guest')
GO

EXEC sp_bindrule 'USERNAME_Domain', 'USERNAME'
GO

And that's it. Now you can use the type throughout the database just as you normally would, and you'll never need to check or verify to make sure that someone slipped in an invalid value ...

CREATE TABLE [User_Logons] (
  [Username] USERNAME NOT NULL,
  [Logon_Date] DATETIME NOT NULL,
  [Success_Indicator] CHAR(1) NOT NULL
    CHECK ([Success_Indicator] IN ('Y','N')),
  PRIMARY KEY ([Username],[Logon_Date])
)