In the grand scheme of things, MySQL and SQL Server operate in different realms. It’s difficult to compare them because MySQL is predominantly used for websites as part of the free LAMP stack (Linux, Apache, MySQL and PHP), while SQL Server’s range is much broader than that.
There’s one neat feature that MySQL has, which is missing from SQL Server, and, while it’s not the end of the world and plenty of workarounds exist, I think it would be a good addition to the database engine.
Let’s say you design a web page, and on that page you need to capture a value represented by a dropdown list or radio button.
In MySQL, if I have a value from a handful of options that I want to persist in a table, I can use the ENUM (short for enumerator) data type for that column.
The ENUM data type provides a list of possible values that can be stored in that column. For example, “YES” or “NO”, the digits 1 through 5, that sort of thing.
If I want to do the same thing in SQL Server, I need to create a separate lookup table and add a foreign key constraint. If I’m sure it will be only two values, I can use a BIT value and use business logic to assign a value to the 0 or 1 that can be stored there.
Those workarounds are fine, but they seem like overkill for the case where you only have two or three values.
In the more than two decades I’ve been working with databases, this has admittedly been only a minor inconvenience, but I think SQL Server would benefit from an enumerator data type.
Let me know what you think on Twitter at @bornsql.
Photo by Felipe Elioenay on Unsplash.com.