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.

Barthelemy

No, other that the solutions proposed (lookup table with FK constraint) there's nothing in SQL Server that works like an "enum" type. – Oceanography

@marc_s: That is too bad. I was hoping that some CLR objects magic could be used to create an enumerated type. – Barthelemy

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.

Loeffler

@Philip Fourie: I can do that, (and have that kind of stuff in the db already) But what "I am looking for [is] compile time checking that a value being compared (ie "Initialized") is part of a list of values." Does this solution offer compile time checking? – Barthelemy

@Vaccano: no, but it offers protection against data modification anomalies. However, you simply set up a matching enumeration if it's that important and cross-check on application start. And +1. – Evvoia

@Vaccano, sorry didn't realise compile time checking was that important when I responded. Sorry I don't know of a better way to it than mentioned here. – Loeffler

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'))
)

Paraphernalia

Answer 3

The only way that I've seen this done is by using a UDF to evaluate whether or not the enum's string representation is valid. It's slow, it's painful, and usually not worth it, but at least you have a way to fail loudly instead of silently.

And remember, you can't RAISERROR in a UDF so you have to cause an intentially cause an error, and log separately.

Ultimately, at the moment, the 'perfect' solution to the problem would be to approach from the other side -- you can achieve this mentality with a code-first ORMs, which would allow you to use native enums in your code, and the corresponding SQL lookups will be created properly in migration.

Here's to hoping we get enums soon, we're feeling a little left out.

Gelsenkirchen