CODEWITHSUNDEEP
sql-serverenums
pkario
pkario

Reputation: 2220

What datatype do you use for enumerations in SQL Server

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?

Upvotes: 3

Views: 13450

Answers (6)

Jony Adamit
Jony Adamit

Reputation: 3416

Here is my answer on a different thread.

The best way in my opinion is to use a lookup table with int, as suggested above, but combining it with code generation to keep with the DRY principle (Don't Repeat Yourself).

Here is the link for the code generation example.

Upvotes: 0

Kev
Kev

Reputation: 119806

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?

Upvotes: 8

JediMindTricks
JediMindTricks

Reputation:

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

Upvotes: 0

devio
devio

Reputation: 37205

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.

Upvotes: 0

recursive
recursive

Reputation: 86084

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.

Upvotes: 4

Martin Brown
Martin Brown

Reputation: 25310

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.

Upvotes: 3

Related Questions