SQL Server equivalent to MySQL enum data type?

Question

Does SQL Server 2008 have a a data-type like MySQL's enum?

Answer 1

The best solution I've found in this is to create a lookup table with the possible values as a primary key, and create a foreign key to the lookup table.

Answer 2

It doesn't. There's a vague equivalent:

mycol VARCHAR(10) NOT NULL CHECK (mycol IN('Useful', 'Useless', 'Unknown'))

Answer 3

CREATE FUNCTION ActionState_Preassigned()
RETURNS tinyint
AS
BEGIN
    RETURN 0
END

GO

CREATE FUNCTION ActionState_Unassigned()
RETURNS tinyint
AS
BEGIN
    RETURN 1
END

-- etc...

Where performance matters, still use the hard values.

Answer 4

Found this interesting approach when I wanted to implement enums in SQL Server.

The approach mentioned below in the link is quite compelling, considering all your database enum needs could be satisfied with 2 central tables.

http://blog.sqlauthority.com/2010/03/22/sql-server-enumerations-in-relational-database-best-practice/

Answer 5

IMHO Lookup tables is the way to go, with referential integrity. But only if you avoid "Evil Magic Numbers" by following an example such as this one: Generate enum from a database lookup table using T4

Have Fun!