What datatype do you use for enumerations in SQL Server

Question

When we serialize an enum from C# to SQL Server we use a NCHAR(3) datatype with mnemonic values for each value of the enum. That way we can easily read a SELECT qry.

How do you save enum to your database?

What datatype do you use?

Answer 1

I use a separate reference data table with ID & Description for each enum. You can group them all into one table with ID, Type & Description, but I have found that you often end up wanting to extend these tables over time and if you group all the reference data into one table it can make life harder in the long run.

Answer 2

I've always just used lookup tables in MSSQL where I would have otherwise used enums in databases that support them. If you use some kind of char type, then you have to use a check constraint to ensure that inserted or updated values are members of the enum.

When the members of the enum change, I've always felt it was easier to add entries to a lookup table than to alter a check constraint.

Answer 3

nchar(3) for mnemonics?

As Martin pointed out, use a lookup table with INT as PK, VARCHAR identifier as UK, and NVARCHAR description.

You can write a stored procedure to script the table values as C# enum or as C# public consts.

Thus the values are documented both in the database and in the C# source code.

Answer 4

A better way would be to store as an int. That way you can deserialise/cast from the DB right back to the correct enum value.

If the enum is likely to be changed in the future then use explicit values e.g.

public enum ActionType
{
  Insert = 1,
  Update = 2,
  Delete = 3
}

The practicalities of storing as a mnemonic must cause you have clashes depending on your mnemonic generating algorithm?

Answer 5

I keep them as integers. its real cute casting them to each other (int)enum and (enum)int :) also my other favorite is dropdownlist.datasource = Enum.GetNames(typeof(EnumType));

Is this too primitive, cuz you are talking about lots of complicated stuff