What is the alternative Enum datatype in Microsoft SQL Server 2008 Management Studio?

Question

I used Enum datatype in phpmyadmin MYSQL with three values, my problem is when I need to import the database into SQL Server 2008 there's no Enum datatype I need to use.

What are the possible I need to modify in my database? Or what are the alternative datatype I need to use?

Thank you

Answer 1

There is nothing exactly like an enum in SQL Server. You essentially have three options.

The most generic is to create a reference table and use a foreign key reference. You can use each value name as the foreign key, guaranteeing that the value is one of a set of values. The advantage of this approach is that you can include a "priority" in the reference table, which can be used for sorting and comparisons.

Next, you can use a check constraint to guarantee that the values for a given column are a given set. This does the validation on the value, but does not have an innate ordering.

The third method would be a user-defined type. This is similar to the second method.