Blog: William McKnight

« Microsoft agrees to acquire ProClarity | Main | Make your plans for the Data Warehousing Insitute in Chicago »

Her Cheatin' Heart Made A Null Out Of Me

You can learn a lot about a data warehouse program by analyzing how it uses nulls. As most of you know, null means "unknown" or "irrelevant". The nullability, or ability to take on the null value, is a dimension of every column in the data warehouse - or any database for that matter. Most columns should not be nullable, but a few should be.
Nulls add extra storage to the column so that the DBMS can record whether the value is null or not. If the null bit/byte is "on", then whatever value may reside in the actual field is irrelevant and will not display. Of course, the other factor is the proper assignment of the null value to those columns that are nullable.

Nulls do not equal zero or spaces. They actually have an entire different meaning than either of those. There may be actual values like "not supplied" or "invalid value" that should be used in place of null (or zeroes or spaces). These descriptive terms are actually more explanatory about the field than null. So nulls get overused sometimes.

But mostly nulls are underused, taking a backseat to zeros and spaces. Nulls don't equal other nulls. And the manner in which nulls participate in aggregate functions like SUM, AVG and COUNT is very logical, but can be tricky. You also can't join on nulls. These basic facts discourage many from using nulls in data warehouses at all. But a little investment in knowledge of nulls can go a long way and afford your program the power that nulls bring.

Thinking about nulls makes me think about... country music of course.

Here are some of my favorite songs when looking at the effective use of nulls in data warehousing:

Stand by Your Null
If My Heart Were Nullable
Kentucky Null
Don't be Null
The Null Road
At the Gas Station of Love, I Got the Self-Service Null
Her Cheatin' Heart Made A Null Out Of Me
You Turned my Lullaby into a Nullaby

Share: