Comment by wvenable
7 hours ago
It's way better to just use a DBMS that supports enums. I know SQL server isn't one of those but I still don't store my coded values as strings.
7 hours ago
It's way better to just use a DBMS that supports enums. I know SQL server isn't one of those but I still don't store my coded values as strings.
The way to do enums in SQL (generally, not just MSSQL) is another table. It's better that they don't offer several ways to do the same thing.
Separate tables can have multiple attributes besides a text description and can be exposed for modification to the application easily so users or administrators can add and modify codes.
A common extra attribute for a coded value is something for deprecation / soft delete, so that it can be marked as no longer valid for future data but existing data can remain with that code, also date ranges its valid for etc, also parent child code relationships.
Enums would be a good feature but they have a much more limited use case for static values you know ahead of time that will have no other attributes and values cannot be removed even if never used or old data migrated to new values.
How do you store them? Also enums are not user configurable normally. It would be a good feature to have them, but they don't work well in many cases.
Typical code tables with code, description and anything else needed for that value which the user can configure in the app.
Sure you can use integers instead of codes, now all your results look like 1, 2, 3, 4 for all your coded columns when trying to debug or write ad-hoc stuff. Also ints are not variable length so your wasting space for short codes and you have to know ahead time if its only going to be 1,2,4 or 8 bytes.