Storing Enumerations In SQL Server

Photo: NeONBRAND

SQL Server does not support Enumerated data types so please stop trying to force them into it.

Enumerations are used to declare a type that groups named constants. Most languages have them in a given form going as far back as Ada. In C# you declare an enumeration as follows:

public enum Colour
{
    Red, 
    Green, 
    Blue, 
    Yellow
}

A common use for enumerations is to apply a label for grouped constant values. So the above may be written as:

public enum Colour
{
    Red = 1, 
    Green = 2, 
    Blue = 3, 
    Yellow = 4
} 

There are lots of articles on the wider web about using enumerations in your language. This post is about the misuse of enumerations which is not only bad design but has a major impact on your project.

Storing an enumeration value in the database may seem appealing. You define your Colour enumeration and define your entity class.

public class Entity
{
    public Colour EntityColour{get; set;}
}

You define your SQL table column to be an int and add the scaffolding code to handle your crud operations. It all works and you move on.

Only a little while later someone else has to write a query on your table containing your Colour values. They have no idea what the values represent. Is 1 red or green or blue? Who knows. They start the process of tracking down who wrote the code. Soon they arrive at your desk but it is 4 months since you last looked at the Colour code. So you have to open the code and find the enumeration declaration and provide them with the details. Now they have the details they end up with a nasty CASE block in their query like

CASE
  WHEN Colour = 1 THEN 'Red'
END AS Colour

Removing magic numbers in our code by using enumerations, we have complicated the data schema. The better approach is to use a lookup table in your database. We then create a relationship via a Foreign Key with the table that stores our Colour data. Again there are plenty of articles on defining database tables and relationships. The point of this post is to show that storing enumeration values in the database is wrong. It leads to a confusing data schema and difficult to maintain code. Using a well-defined database schema will result with an extra Colour type in your code and an easy to follow validated dataset that is built on a standard join.

This article has made enumerations sound like they shouldn’t be used and that’s not that case at all. In a future post I will cover how and when to use them effectively.

If you have any thoughts around this post then please contact me via twitter or email.