CODEWITHSUNDEEP
mysqldatabasesql-server-2005enums
matt.mercieca
matt.mercieca

Reputation: 873

Does SQL Server 2005 have an equivalent to MySql's ENUM data type?

I'm working on a project and I want to store some easily enumerated information in a table. MySql's enum data type does exactly what I want: http://dev.mysql.com/doc/refman/5.0/en/enum.html . Is there an equivalent in SQL Server 2005?

I know I could store the possible values in a type table with a key, but I'd rather not have to link back to it for descriptions. Our database standards don't allow us to link on non-integer or uniqueidentifier fields, so storing the possible keys as characters is out as well.

Upvotes: 28

Views: 12122

Answers (2)

Nikki9696
Nikki9696

Reputation: 6348

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'

Upvotes: 24

Bill Karwin
Bill Karwin

Reputation: 562290

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.

Upvotes: 17

Related Questions