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?

  • 3
    This answer has an insight on usage of NULL dba.stackexchange.com/questions/5176/… – Derek Downey Aug 30 '11 at 21:24
  • 9
    Really? Why does RDBMS allow us to use NULL at all, if we shouldn't use them? There's nothing wrong with NULL as long as you know how to deal with them. – Fr0zenFyr Aug 13 '13 at 6:19
  • 3
    Was this a BI data modeling? You generally should not allow nulls in fact tables... otherwise, nulls are you friends when used properly. =) – sam yi Sep 3 '13 at 18:08
  • 2
    @Fr0zenFyr, just because an RDBMS allows us to do something it is not necessarily a good idea to do so. Nothing forces us to declare a primary key or a unique key in a table, but with few exceptions we do anyhow. – Lennart Apr 4 '16 at 18:53
  • 3
    I think a complete treatment of this subject would have to make reference to Codd's original requirement that an RDBMS must have a systematic way of treating missing data. In the real world, there are situations where a location for data is created, but there is no data to put in it. The Data Architect has to come up with some response to this, whether it involves database design, application programming, or both. The SQL NULL is less than perfect in meeting this requirement, but it's better than nothing at all. – Walter Mitty Feb 24 '17 at 12:26
up vote 213 down vote accepted

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.

  • 1
    "some arbitrary token value to represent the fact that it is unknown" this is known as a sentinel value – Alexander Oct 7 '17 at 6:23
  • 2
    But what prevents you from creating separate table birth_date where you store birth dates? If birth date is unknown then just don't insert date of birth into birth_date. Nulls are disaster. – Eldar Agalarov Nov 16 '17 at 23:07
  • 5
    @EldarAgalarov That sounds like Trump reasoning (“disaster” why? How? For whom? Your opinion that something is a "disaster" doesn't make it so). Anyway birth date is just one example. If you have personnel or members or customers that have 15 potentially nullable columns, are you going to create 15 secondary tables? What if you have 50? What if your DW fact table has 500? Maintenance to keep big bad scary NULLs out of your database becomes 10x as bad as any “disaster” you’re afraid of... – Aaron Bertrand Nov 17 '17 at 0:43
  • 2
    @AaronBertrand if your table has 15 potentially nullable columns, it smells really bad ^^ Not that a huge number of column is inherently bad, but it may indicate a bad design OR required denormalization. But it will raise questions. – programaths Jan 7 at 11:27
  • 2
    @Wildcard So you’ve never seen people store 1900-01-01 to avoid having a NULL date/time value? Ok then. Also, NULL = unknown and unknown = false. I'm not sure what problems this might cause other than people aren't born knowing that (like they aren't born knowing a lot of things inherent in a complex RDBMS). Again, waving hands and saying "Problem! Disaster!" doesn't make it so. – Aaron Bertrand Sep 18 at 13:02

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 otherwise 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”.

  • 3
    Your link to "How To Handle Missing Information Without Nulls" shows quite nicely why we can't do without nulls: Several of the suggestions would be impossible to implement in a rational way on the major RDBMSs as they currently stand. – Jack Douglas Sep 24 '11 at 12:28
  • 7
    Jack: Right, but “the current implementations can't do it” is not an argument for the status quo :-) – bignose Sep 24 '11 at 12:32
  • 17
    Is that kind of like saying we shouldn't fly because planes aren't perfect? – Aaron Bertrand Sep 27 '11 at 11:48
  • 9
    No, it's saying that the vendors should stop invoking excuses for nulls that might have been valid ones forty years ago, but have long outlived their reasonable retention period. I/O times are no longer in the order of magnitude of 80ms. Single CPU cycles are no longer in the order of magnitude of microseconds. Memory limits are no longer in the order of magnitude of a few Megs. Unlike forty years ago, the hardware speeds and capacities needed for working without nulls now DO exist with the cost not being prohibitive. He's saying it's time to move on. – Erwin Smout Oct 5 '12 at 13:30
  • 2
    "NULL confusion" link is dead. – jpmc26 Feb 17 '17 at 22:29

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.

  • 1
    I suggest having a user-defined set of different kinds of null, and a user-defined multi-valued logic to go with them :p – Jack Douglas Sep 24 '11 at 12:32
  • 11
    Those aren't the only options. You exclude the normalisation alternative: Instead of columns which may or may not have a value, use another table which may or may not have a corresponding row for the first table. The meaning of the presence or absence of a row is entailed in the meaning of the tables, and there's no special-casing of NULL or sentinel values etc. – bignose Sep 24 '11 at 22:35
  • 5
    The presence of NULL does not necessitate special-casing or sentinel values. Those are just symptoms of how some folks decide to deal with NULLs. – Aaron Bertrand Sep 25 '11 at 20:43
  • It's worth noting that '' is distinct from null on PostgreSQL (though not Oracle) and so gives you a two-fold marker, and you could use 0 for numeric columns. The problem with 0 though is that it doesn't work for foreign keys. – Chris Travers Nov 7 '13 at 6:43

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.

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.

Unknown Nullable and 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.

  • 1
    I would just change it so that there is no TerminationDate in the employee records, but have a table for TerminatedEmployee which employees are moved to (not copied) by the application when they are terminated. Obviously this works nicely with the Account table because there will be no linked account on the TerminatedEmployee table. If you still need the phone numbers, I would reverse the foreign keys so the employee and terminated employee tables have the id of the phone number instead of the other way around. – Programster Sep 13 '17 at 9:01
  • 2
    I could literally go on for days about why this would be bad. Redundant tables, bad SQL practices, making it so your developers would have to look in two places for employee data, issues with reporting, issues with direct URIs to an employee that doesn't exist (has been moved), and the list goes on and on. It is completely fine to have NULLS for fields that will someday have a value, it's another story to have fields that never get filled and never have a use. A number of potential issues and workarounds to make this work wouldn't be worth the small issue of checking for NULL on a field. – Nicholas Aguirre Sep 14 '17 at 13:23
  • 1
    I disagree. The only thing redundant is that null field for the termination date that may never get filled. Developers only have to look in the appropriate table for the data they want, and could improve performance. If for some reason, you want both terminated and non-terminated employees, its resolved by a join, but 90% of the time your application will probably want one or the other. I think the layout I specified is better because it would be impossible to have a termination date on an employee and for him to still have an account. – Programster Sep 14 '17 at 17:27
  • 2
    I didn't say redundant data, I said redundant tables. Plus, any change to the employee tables must trickle down to the terminated tables; this makes the app error-prone and it makes the developer's job much harder. Plus, a Termination date field will get filled for almost everyone. It is wasteful and problematic to create a second identical table structure and also move data around. Not to include the testing each time to make sure the table data was moved and cleaned. It is bad practices to remove data from a table, even if only to move it. If you are so concerned with a single field that... – Nicholas Aguirre Sep 18 '17 at 14:26
  • 1
    ...that will almost always be filled in time then make a terminated table with a 1to1 relationship back to the employee. I work with a variety of databases all day both as a DBA and as a developer and I am glad I have yet to come across one with the structure you proposed. Especially from a developer's point of view, it would be a nightmare to write and error check everything because you wouldn't know which table it was coming from. Even writing a join, the data returned to the software would have a field with null data which would still require you to test that as well. – Nicholas Aguirre Sep 18 '17 at 14:39

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.

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.

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.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.