Does SQL Server 2005 have an equivalent to MySql's ENUM data type?
Does this work for you?
From http://blechie.com/wtilton/archive/2007/08/24/303.aspx
Create table...
MySQL:
ColumnName ENUM('upload', 'open', 'close', 'delete', 'edit', 'add') DEFAULT 'open'SQL Server:
ColumnName varchar(10) CHECK(ColumnName IN ('upload', 'open', 'close', 'delete', 'edit', 'add')) DEFAULT 'open'
One characteristic of MySQL's ENUM data type is that it stores only a numeric index into the list of values, not the string itself, on each row. So it's usually more storage-efficient. Also the default behavior when you sort by an ENUM column is to sort by the numeric index, therefore by the order of elements in the ENUM.
Nikki9696 suggests using a VARCHAR column with a CHECK constraint. This satisfies the restriction of values to a certain short list of permitted values, but it doesn't simulate the storage efficiency or the special sort order.
One way to get both behaviors is to declare the column as an integer foreign key into a lookup table, in which you store each permitted string.