Re: NULL values or Zero in numeric fields?

Francois Lachance <francois.lachance_at_sasknet.sk.ca> writes:  

>We have a few tables with numeric columns. By default, if you don't
>save a value in those columns, they are NULL values. Is there an
>advantage to leaving these empty rows as NULL? Users that are doing
>queries directly against the database are asking that Zero be put in
>the rows where no value has been specified. Programmers find it
>annoying to have to make sure that calculations are not done on NULL
>values since it returns a NULL if you do.
>
>What does that do as far as data storage is concerned? Will a zero
>take more space then a NULL?
 

Think of null as "unknown value" or "missing data point" and only use it for those situations. If the value is known to be zero, then store a zero.  

Unfortunately, Oracle's implementation of null for character columns is treated as identical to a zero-length string (the value ''). So for strings, you cannot use the theoretically proper "unknown value" interpretation, since it cannot be distinguished from the "known to be an empty string" value. This is an Oracle screwup, not a problem with the relational model.  

But, again, with numeric columns, you can use nulls for what they were intended -- unknown values.  

• Rick --

(Rick Rutt is a system architect living and working in Midland, Michigan.) Received on Wed Nov 29 1995 - 00:00:00 CET