CODEWITHSUNDEEP
c#sql-servert-sqlenums
Andzej Maciusovic
Andzej Maciusovic

Reputation: 4476

T-SQL enum number or string

I have a table Drivers with columns Id, Name, Status. In C# I have an enum for driver status

public enum DriverStatus
{
   Online = 0,
   Offline = 1,
   Busy = 2,
   SoonFree = 3
}

Currently in the database, I use a varchar data type for the Status column and this means I have records like:

1     John      Online
2     Elsy      Offline

This seams to be bad and I think this need to be changed to status column type tinyint because:

  1. T-SQL Tinyint is only one byte size with range 0-255.
  2. Now it is not possible to normally sort by status column because it is varchar so it sorts in alphabetical order not in enum priorities.
  3. If I rename DriverStatus enum value name I also need to update database to be consistent.

Then I asked others why we use varchar for enum columns the only reason was that it is easier to debug as you see text not number like 0 or 3. Is where any really good reasons to have strings for enums in the database?

Upvotes: 0

Views: 1317

Answers (1)

Sam Axe
Sam Axe

Reputation: 33738

It is absolutely better to use a Lookup Table for enum values.

Advantages:

  • Usually less room in the database is used.
  • Renaming the display value is very easy.
  • Globalization is possible.
  • It is easy to retire values that are no longer used.
    • My lookup tables always contain three fields: [the ID/Primary Key], Name, and Enabled

Upvotes: 2

Related Questions