SQL: The need for enumerations in T-SQL

SQL: The need for enumerations in T-SQL

I recently wrote about the need for T-SQL to have constants. I received a lot of feedback on that. Everyone seemed to agree, although one or two asked “why bother as Microsoft isn’t really listening now about enhancing T-SQL?”. I understand that sentiment but I don’t think things are quite as bleak as some are suggesting. I currently sense a stirring within the product group, where there is a new interest in developers.

Constants are important but another core aspect of most development languages is the ability to define enumerations.

What are enumerations?

Enumerations are a static ordered set of values. For example, I might have the need to store a column called NextAction and the allowable values are:

  • Open
  • Process
  • Close
  • Delete

In T-SQL today, there are two options that we might use. We could define a simple table called Actions with one column called ActionName, add those four rows, then define a foreign key from the NextAction column to the Actions.ActionName column.

That would work. If we wanted them to appear in the order above though, we’d need to have another column (perhaps an identity or sequence) in the Actions table to provide that order.

Another option that you might use today is to define a set of integer values for each of these names, and use the integer values to define the order. To limit the range of allowed values in the NextAction column, you could add a CHECK constraint.

But that’s all quite messy, given what you’re really trying to achieve.

The other option that I didn’t mention is that you can do this with SQLCLR but given it’s not supported on Azure SQL Database (where we do most of our work now), I’ve discounted it.

Other Databases

PostgreSQL has an implementation of enumerations.

I could start by defining the enumeration like this:

CREATE TYPE action AS ENUM ('Open','Process','Close','Delete');

Once that’s defined, you can use it in a table definition.

CREATE TABLE processes
(
    ...
    last_action action,
    next_action action
    ...
);

One key difference relates to ordering. By declaring the enum, I’ve also defined that ‘Close’ is greater than ‘Process’, unlike the string values. In PostgreSQL, if you do want to compare the strings literally, you do it like this:

WHERE last_action::text = other_table.next_action::text

If these were implemented in SQL Server, a CAST would work fine for this.

The way they are implemented in PostgreSQL, they are all case-sensitive. I wouldn’t want that. I’d want it to follow the database case-sensitivity rules.

Also, the way they are implemented in PostgreSQL, you use standard strings to find values:

WHERE last_action = 'Close'

I would much rather it was implemented like it is in C#, etc. where it would be:

WHERE last_action = action.Close

This would also help with Intellisense. As soon as you type action and the period, a list of available values could appear.

Summary

Enumerations increase the abstraction level of the code, and reduce the number of bugs. SQL Server system stored procedures, functions, etc. are littered with “magic value” parameters. All of them would be improved by having enumerations available.

This would greatly reduce the number of bugs we all have to deal with, and increase overall code quality.

As expected, there’s already a long-term request for enums. You can vote for them and add your comments here.

2020-03-19