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

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 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.