Why shouldn't we allow NULLs?

Question

I remember reading this one article about database design and I also remember it said you should have field properties of NOT NULL. I don't remember why this was the case though.

All I can seem to think of is that, as an application developer, you wouldn't have to test for NULL and a possible nonexistent data value (for instance, an empty string for strings).

But what do you do in the case of dates, datetime, and time (SQL Server 2008)? You'd have to use some historic or bottomed-out date.

Any ideas on this?

Answer 1

I think the question is poorly phrased, as the wording implies that you've already decided NULLs are bad. Perhaps you meant "Should we allow NULLs?"

Anyway, here is my take on it: I think NULLs are a good thing. When you start preventing NULLs just because "NULLs are bad" or "NULLs are hard", you start making up data. For example, what if you don't know my birth date? What are you going to put in the column until you know? If you're anything like a lot of anti-NULL folks, you're going to enter 1900-01-01. Now I'm going to be placed in the geriatric ward and probably get a call from my local news station congratulating me on my long life, asking me my secrets to living such a long life, etc.

If a row can be entered where it is possible that you don't know the value of a column, I think NULL makes a lot more sense than picking some arbitrary token value to represent the fact that it is unknown - a value which others will have to already know, reverse engineer, or ask around to figure out what it means.

There is a balance, though - not every column in your data model should be nullable. There are often optional fields on a form, or pieces of information that otherwise don't get collected at the time the row is created. But that doesn't mean you can defer populating all of the data. :-)

Also the ability to use NULL can be limited by crucial requirements in real life. In the medical field, for example, it can be a life-or-death matter to know why a value is unknown. Is the heart rate NULL because there wasn't a pulse, or because we haven't measured it yet? In such a case, can we put NULL in the heart rate column, and have notes or a different column with a NULL-because reason?

Don't be afraid of NULLs, but be willing to learn or dictate when and where they should be used, and when and where they shouldn't.

Answer 2

Established reasons are:

• NULL is not a value, and therefore has no intrinsic data type. Nulls need special handling all over the place when code that relies on actual types might also receive the un-typed NULL.

• NULL breaks two-value (familiar True or False) logic, and requires a three-value logic. This is far more complex to even implement correctly, and is certainly poorly understood by most DBAs and just about all non-DBAs. As a consequence, it positively invites many subtle bugs in the application.

• The semantic meaning of any specific NULL is left to the application, unlike actual values.

  Semantics like “not applicable” and “unknown” and “sentinel” are common, and there are others too. They are frequently used simultaneously within the same database, even within the same relation; and are of course inexplicit and indistinguishable and incompatible meanings.

• They aren't necessary to relational databases, as argued in “How To Handle Missing Information Without Nulls”. Further normalisation is an obvious first step to try ridding a table of NULLs.

This doesn't mean NULL should never be allowed. It does argue that there are many good reasons to disallow NULL wherever feasible.

Significantly, it argues for trying very hard – through better schema design, and better database engines, and even better database languages – to make it feasible to avoid NULL more often.

Fabian Pascal responds to a number of arguments, in “Nulls Nullified”.

Answer 3

I disagree, nulls are an essential element of database design. The alternative, as you alluded too, would be a proliferation of known values to represent the missing or unknown. The problem lies with null being so widely misunderstood and as a result being used inappropriately.

IIRC, Codd suggested the current implementation of null (meaning not present/missing) could be improved by having two null markers rather than one, "not present but applicable" and "not present and not applicable". Can't envisage how relational designs would be improved by this personally.

Answer 4

Let me start off by saying I am not a DBA, I am a developer by heart and I maintain and update our databases based on our needs. That being said, I had the same question for a few reasons.

1. Null values make development more difficult and bug prone.
2. Null values make queries, stored procedures, and views more complex and bug prone.
3. Null values take up space (? bytes based on fixed column length or 2 bytes for variable column length).
4. Null values can and often affect indexing and mathematics.

I spend a very long time sifting through the loads of responses, comments, articles, and advice all over the internet. Needless to say most of the information was about the same as @AaronBertrand's response. Which is why I felt the need to respond to this question.

Firstly I want to set something straight for all future readers... NULL values represent unknown data NOT unused data. If you have an employee table that has a termination date field. A null value in the termination date is because it is a future required field which is currently unknown. Every employee be it active or terminated will at some point have a date added to that field. That is in my opinion the one and only reason for a Nullable field.

That being said the same employee table would most likely hold some kind of authentication data. It is common in an enterprise environment that employees will be listed in the database for HR and accounting but don't always have or need authentication details. Most of the responses would lead you to believe it is okay to null those fields or in some cases create an account for them but never send them the credentials. The former will cause your development team to write code to check for NULLs and to deal with them accordingly and the latter poses a huge security risk! Accounts that are never used yet in the system only increase the number of possible access points for a hacker, plus they take up valuable database space for something that is never used.

Given the information above, the best way to deal with nullable data that WILL be use is to allow for nullable values. It is sad but true and your developers will hate you for it. The second type of nullable data should be put in a related table (IE: Account, Credentials, etc) and have a One-to-One relationship. This allows for a user to exist without credentials unless they are needed. This removes the extra security risk, valuable database space, and provides for a much cleaner database.

Below is a very simplistic table structure showing both the required nullable column and a One-to-One relationship.

I know I am a little late to the party since this question was asked years ago but hopefully this will help to shed some light on this issue and how best to deal with it.

Answer 5

Apart from all the issues with NULL confusing developers, NULLs have another very serious drawback: Performance

NULL'able columns are a disaster from a performance perspective. Consider integers arithmetic as an example. In a sane world without NULL, it is "easy" to vectorise integer arithmetic in the database engine code using SIMD instructions to perform pretty much any calculation at speeds faster than 1 row per CPU cycle. However, the moment you introduce NULL, you need to handle all the special cases that NULL creates. Modern CPU instruction sets (read: x86/x64/ARM and GPU logic too) are simply not equipped to do this efficiently.

Consider division as an example. At a very high level, this is the logic you need with a non null integer:

if (b == 0)
  do something when dividing by error
else
  return a / b

With NULL, this becomes a little more tricky. Together with b you will need an indicator if b is null and similarly for a. The check now becomes:

if (b_null_bit == NULL)
   return NULL
else if (b == 0) 
   do something when dividing by error
else if (a_null_bit == NULL)
   return NULL
else 
   return a / b

The NULL arithmetic is significantly slower to run on a modern CPU than the not null arithmetic (by a factor of around 2-3x).

It gets worse when you introduce SIMD. With SIMD, a modern Intel CPU can perform 4 x 32-bit integer divisions in a single instruction, like this:

x_vector = a_vector / b_vector
if (fetestexception(FE_DIVBYZERO))
   do something when dividing by zero
return x_vector;

Now, there are ways to handle NULL in SIMD land too, but this requires using more vectors and CPU registers and doing some clever bit masking. Even with good tricks, the performance penalty of the NULL integer arithmetic creeps into the 5-10x slower range for even relatively simple expressions.

Something like the above holds for aggregates and to some extent, for joins too.

In other words: The existence of NULL in SQL is an impedance mismatch between database theory and the actual design of modern computers. There is a pretty good reason NULL confuses developers - because an integer cannot be NULL in most sane programming languages - that is just not how computers work.

Answer 6

Interesting questions.

All I can seem to think of is that, as an application developer, you wouldn't have to test for NULL and a possible nonexistent data value (for instance, an empty string for strings).

It's more complicated than that. Null has a number of distinct meanings and one really important reason not to allow nulls in many columns is that when the column is null this then means one and only one thing (namely that it didn't show up in an outer join). Additionally it allows you to set minimum standards of data entry which is really helpful.

But what do you do in the case of dates, datetime, and time (SQL Server 2008)? You'd have to use some historic or bottomed-out date.

That illustrates a problem with nulls right away, namely that a value stored in a table can mean either "this value does not apply" or "we don't know." With strings, an empty string can serve as "this does not apply" but with dates and times, there is no such convention because there is no valid value which conventionally means this. Typically there you will be stuck using NULLs.

There are ways of getting around this (by adding more relations and joining) but those pose the exact same semantic clarity problems that having NULLs in the database do. For these databases I wouldn't worry about this. There just isn't anything you can do about it really.

EDIT: One area where NULLs are indispensable is in foreign keys. Here they typically have only one meaning, identical to the null in outer join meaning. This is an exception to the problem of course.

Answer 7

Wikipedia's article on SQL Null has some interesting remarks about the NULL value, and as a database-agnostic answer, as long as you are aware of the potential affects of having NULL values for your specific RDBMS, they are acceptable in your design. If they were not, you wouldn't be able to specify columns as nullable.

Just be aware of how your RDBMS handles them in SELECT operations such as mathematics, and also in Indexes.

Answer 8

Wow, the correct answer "Don't allow NULLs when you don't have to because they degrade performance" is somehow the last rated answer. I will upvote it and elaborate. When a RDBMS allows NULLs for a non-sparse column, that column is added to a bitmap that tracks whether the value is NULL for each individual row. So by adding NULL-ability to a column in a table where all columns do not allow NULLs, you are increasing the storage space required to save the table. Further, you are requiring the RDBMS to read and write to the bitmap, degrading performance on all operations.

Further, in a number of instances, allowing NULLs will break 3NF. While I am not a stickler for 3NF like many of my colleagues, consider the following scenario:

In the Person table there is a column, called DateOfDeath, which is nullable. If a person has died, it will be filled in with their DateOfDeath, otherwise it will be left NULL. There is also a non-nullable bit column called IsAlive. This column is set to 1 if the person is alive, and 0 if the person is dead. The vast majority of the stored procedures use the IsAlive column, they care only if a person is alive, not their DateOfDeath.

However, the IsAlive column breaks database normalization, because it is completely derivable from DateOfDeath. But since IsAlive is hard-wired into the majority of SPs, the straightforward solution is to make DateOfDeath non-nullable, and assign a default value to the column in the event the person is still alive. The few SPs that use DateOfDeath can then be rewritten to check the IsAlive column, and only honor the DateOfDeath if the person is not alive. Again, since the majority of SPs care only about IsAlive (a bit) and not DateOfDeath (a date) using this pattern speeds up access considerably.

A useful T-SQL script for finding nullable columns with no NULLs across all schemas is:

select 'IF NOT EXISTS (SELECT 1 FROM ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ' WHERE ' + QUOTENAME(c.name) + ' IS NULL)
    AND (SELECT COUNT(*) FROM ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ') > 1 PRINT ''' + s.name + '.' + t.name + '.' + REPLACE(c.name, '''', '''''') + ''''
    from sys.columns c
    inner join sys.tables t ON c.object_id = t.object_id
    inner join sys.schemas s ON s.schema_id = t.schema_id
    where c.is_nullable = 1 AND c.is_computed = 0
    order by s.name, t.name, c.name;

If you run this on a copy of your production database you can find the columns developers marked as allowing NULLs that have no NULLs in practice. The vast majority of these can be marked as NOT NULL, thereby increasing performance and lowering storage space.

It may not be possible to eliminate all NULLs in all tables and still have a clean design, but there is a considerable advantage in eliminating as many NULLs as possible. The optimizer works much faster with this information, and if you can eliminate all NULLs in a table you can regain a considerable amount of storage space.

I know that performance isn't something that DBAs think about all that much, but you can only throw a limited amount of memory and processor power at a solution, a some point you're going to have to start thinking about logical and physical design.

Also note that this is only for true RDBMSes and I am basing the technical portion of my answers off SQL Server. The listed T-SQL to find nullable columns without nulls is also from SQL Server.