Re: NULL values or Zero in numeric fields?

francois.lachance_at_sasknet.sk.ca (Francois Lachance) wrote:
>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?
>
>Thanks for any help that you can provide.
>
>
>+===================================================================+
>+ +
>+ Francois Lachance +
>+ Data Administrator +
>+ Saskatchewan Highways and Transportation +
>+ Regina, Saskatchewan +
>+ Canada +
>+ +
>+===================================================================+
>
>
Answers:

1. Yes there is a storage advantage to leaving columns NULL. No space is taken up in the table when a column is NULL.
2. Programmers are correct to not want to put 0's there. A 0 denotes something, NULL denotes absence of something. The difference is subtle, but signficant.
3. End-users need to be trained in the correct use of tools (or special tools bult for them). They will have to learn how the data works and what the significance of it is.

Just my opinion, of course.

rr Received on Wed Nov 29 1995 - 00:00:00 CET