Re: NULL versus NOT NULL usage

From: IMTB Data Management <datamgmt_at_istar.ca>
Date: 1998/06/01
Message-ID: <3572F27A.163A_at_istar.ca>#1/1


RABK wrote:
>
> I am researching the benefits of NOT NULL versus NULL datatypes and am
> interested in the viewpoints of the exp dba, client developers.
>
> Describe the benefits of using a table that has NOT NULL versus a NULL data
> type.
>
> Do NULLS in general cause more heartache for client developers? Why?
>
> Do NOT NULL save enormous amounts of space.
>
> What issues arise from querying fields that have NULLS in them?

Null can save space but they also require additional space for the null indicator on every column. You'll have to do the math for your particular use of the column.

A big question here is 'what does a null value mean?'. I believe it was C.J. Date who, a number of years ago, came up with something like 27 different possible meanings for a null column (i.e. there was no value available, the value was in error, it was deleted, it was not applicable, etc). You'll have to consider how nulls fit with your business rules.

One area where nulls are very useful is in aggregates and arithmetic functions. If you do an average of a number of rows, for example, the sum gets divided by the number of not-null values - very different than if you load up unknown/unapplicable fields with zeroes.

Rick Gebethner Received on Mon Jun 01 1998 - 00:00:00 CEST

Original text of this message