Enumerated types in SQL Server 2008?

Question

Is there some kind of mechanism in SQL Server to allow Enumerated type like functionality?

For example, if I have a column Called "UpdateStatus" it usually gets setup with single letter values like so:

1. D
2. X
3. U
4. I

This could equate to a lot of things. That leads to confusion. The alternative is to have it be a string column like this:

1. Downloaded
2. Deleted
3. Updated
4. Initialized

But that has its own problems. Eventually someone is going to write something like this: where UpdateStatus = 'Initalized' (spelled wrong). Plus I hear that keying off of strings is not all that performant.

So, is there any kind of enumerated type for SQL Server that can help out with this? Basically I am looking for compile time checking that a value being compared (ie "Initialized") is part of a list of values.

I am using SQL Server 2008.

Answer 1

Why not have lookup table that contains the code and description. Creating a foreign key to this lookup table will result in only valid codes being used.

Answer 2

Besides lookup tables (FKs), in simple cases, you can use check constraints:

CREATE TABLE my_table ( 
    UpdateStatus VARCHAR2(11) 
      CHECK( UpdateStatus IN ('Downloaded', 'Deleted', 'Updated', 'Initialized'))
)