SQL Server Equivalent to MySQL Enum Data Type

SQL Server equivalent to MySQL enum data type?

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

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

Create enum in SQL Server

It's better to properly normalize your model:

create table user_rank
(
   id integer primary key, -- no identity, so you can control the values
   rank varchar(20) not null unique
);

insert into user_rank (id, rank)
values
  (1, 'Fresh Meat'),
  (2, 'Intern'),
  (3, 'Janitor'),
  (4, 'Lieutenant'),
  (5, 'Supreme being');

CREATE TABLE [users]
(
   ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
   username varchar(255),
   password varchar(255),
   mail varchar (255),
   rank integer not null default 1, 
   constraint fk_user_rank foreign key (rank) references user_rank (id)
);

The dropdown on your web site can easily be populated by querying the user_rank table.

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'

Storing enum values in database

There is no definite design rule (that I know of), but I prefer approach #1.

1. Is the approach I prefer. It's simple, and enums are usually compact enough that I start remembers what the numbers mean.
2. It's more readable, but can get in the way of refactoring or renaming your enumeration values when you want to. You lose some freedom of your code. All of the sudden you need to get a DBA involved (depending on where/how you work) just to change an enumeration value, or suffer with it. Parsing an enum has some performance impact as well since things like Locale come into play, but probably negligible.
3. What problem does that solve? You still have unreadable numbers in a table somewhere, unless you want to add the overhead of a join. But sometimes, this is the correct answer too depending on how the data is used.

EDIT:
Chris in the comments had a good point: If you do go down the numeric approach, you should explicitly assign values so you can re-order them as well. For example:

public enum Foo
{
     Bar = 1,
     Baz = 2,
     Cat = 9,
     //Etc...
}

How to define ENUM in SQL Server 2005?

Use one or more scalar UDFs?

One per constant:

• dbo.CONST_Bicycle returns 1
• dbo.CONST_Car returns 2

One per enum:

• dbo.CONST_Types('Bicycle') returns 1
• dbo.CONST_Types('Car') returns 2

Or use a table with ID, Name per enum

Use a client side enum to match this (perhaps with validation against the table solution)

There is no quick or clean way to do this like there is in .net (as per your comment).

---

Related Topics