Opinion: Which SQL Server columns should be nullable - no place for Magic values

In  a recent blog post, I wrote about the ANSI_NULLS option for tables and after that I had several more questions related to NULL values.

Note that I mentioned NULL values, not values of NULL, and not values equal to NULL. That’s because “being NULL” is a state that a value is in, not itself a value. That’s why our queries say IS NULL and not = NULL.

Now because of that, many developers see values that  are NULL as a pain in the neck as they have to be dealt with separately, unlike other values. This can lead to many poor design decisions but I want to start today with the decision to use magic values.

Some developers will just decide that their databases will never have values that are NULL.

The problem with designs like this is if they have a column that should be NULL (because no sensible value could go there), instead they tend to put a magic value there instead.

[caption id=“attachment_3015” align=“alignnone” width=“746”] Image by Jez Timms[/caption]

Magic values are values that are stored like any other value but are intended to mean something else.

The problem with magic values is that as soon as you use a real value to represent something that means something different, every piece of software that deals with the database, and anyone who queries the database directly, has to understand the substitution trick. These go wrong all the time. Inevitably, someone doesn’t follow and these values have a way of leaking into the real world.

Here’s a simple example:

Now we don’t know where exactly this went wrong. It could be as simple as the UI not knowing how to display a non-existent value, or the value 1/01/001 was stored instead of the value being NULL. Either way, it shows nonsense. What it no doubt should say is never.

This is very common with dates. I work on systems all the time that assume that instead of NULL dates use 1/1/3000 or 1/1/9000, etc. Invariably these values leak out into the real world.

There is a wonderful story of a country where OCR machines had been introduced to read tax returns instead of humans and, by design, when they couldn’t read the values, they inserted all nines. Pity the people writing the next layer of code didn’t get the memo. The huge values made their way right into the country’s budget calculations before being identified as wrong.

This is surprisingly common. Here’s another example:

I’m guessing that they decided to store 9999 for wind speed when they didn’t have one, and -999 for precipitation when the value was missing. Again, pity the UI designer didn’t know that, or didn’t test for that.

Magic values are just one common problem. Next week, we’ll discuss another.

2018-04-10