Enumeration is a list that can be ordered in several different ways. A list of different items that have a common denominator is a set. In SQL, there are many lists that may have a finite number of related data that needs to be accessed within specific parameters. Since there are different flavors of SQL, enumeration is handled differently by each as well.
An enumerated list is very handy if you are requesting parameters to be input, but you want to limit the options and avoid redundancy. For instance, in setting up a database (DB) where someone would enter information to take delivery of merchandise, there are fields that would require freely adding data to include in the database and data that you know needs to remain finite. In this DB, you will want people to add their names, address, and a phone number, since these will be unique in every circumstance. They may also need to be updated regularly, and so the database will need to have this information stored per record. However, the delivery options are not. You will not want your user to increase the database size with unique instances of the same, limited numbers of delivery options. Instead, you will want to limit their choices to something like “US Mail,” “UPS,” “FedEX,” or “Will-call.”
You have a set of delivery options that will rarely ever change, but may change if another option is available, like if a new carrier entered the industry. You also do not want to take up precious memory space with thousands of redundant terms, and will want to avoid misspellings, thus preserving the integrity of the system. Enumeration is the answer to keeping the database clean and manageable.
The various flavors of SQL treat enumeration differently, each with advantages and disadvantages. MySQL uses an ENUM code that allows for the database to have lists within the code. The advantage is that the enumerated lists are immediately available, and provides a faster delivery of information. The downsides are since the lists and sets are in the code, if there are changes, it requires an update of code, and in long lists, like the set of all countries in the world, it will be large and difficult to maintain.
T-SQL does not use enumeration. Instead, lists are in tables, making the table the set. By putting sets into tables, it creates a specific location for looking-up constrained data that needs to be used often. It is also easier to update information by a DB manager who would only need to update one field. In the case of countries, if a country changes its name, becomes a different country, or closes off to delivery, the DB manager only changes it one place. The upside is flexibility and ease of updating, as well as increasing the speed of the DB. The downside is it requires a greater mastery of writing the code that will look-up and write the information in forms as it is needed.