Now I want to talk a bit about when we should be using NULLs and when we shouldn’t, inspired again by some things I’ve seen in legacy code a few days ago. And when I say using, I mean when should we allow values to be NULL, in tables, and parameters, and anything else.
The first thing to remember is what does a NULL represent. It’s very simple, NULL means “I don’t know”. If you keep that in mind, and you’re practising some good development design where everything has a point, you should be able to tell if it’s possible to be unsure about any particular value for a row.
One of the tricky parts about NULL values and legacy code is that if I need to add a new column and there is not a good default, either I make the column nullable (regardless of whether it should be) or I take downtime by blocking table access until my change is in place. As a result, quite often, I simply need to make something nullable because I can’t afford to block the table that long.