Enumerated types in SQL Server 2008?

Question

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

For example, if I have a Called “UpdateStatus” column, it usually gets the installation with single-letter values ​​like this:

• D
• X
• U
• I

It can be compared to many things. This leads to confusion. An alternative is to have this row column as follows:

• Uploaded
• Removed
• Update
• Initialized

But this has its problems. In the end, someone is going to write something like this: where UpdateStatus = 'Initalized' (misspelled). Plus I heard that disabling strings is not that important.

So, is there any type of enumeration for SQL Server that can help with this? I basically look for compile time by checking that the value being compared (ie, “Initialized”) is part of the list of values.

I am using SQL Server 2008.

Answer 1

Why not have a lookup table containing code and description. Creating a foreign key in this lookup table will only use valid codes.

Answer 2

In addition to search tables (FK), in simple cases you can use control restrictions:

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

Answer 3

The only way I've seen this is to use UDF to evaluate whether the enumeration string representation is valid. It is slow, painful and usually not worth it, but at least you have a way to fall through loudly rather than quietly.

And remember that you cannot RAISERROR in UDF, so you must call the cause of the error and write it separately.

Ultimately, the “perfect” solution to the problem will be to approach from the other side - you can achieve this mentality using code-based ORM, which allows you to use your own enumerations in your code, and the corresponding corresponding SQL queries will be correct created during migration.

Here, to hope, we will soon receive the listings, we are a little down.