Re: NULL versus NOT NULL usage

From: Brian Ceccarelli <nsysbnc_at_acs.ncsu.edu>
Date: 1998/06/01
Message-ID: <3572CDD7.1A36_at_acs.ncsu.edu>#1/1

RABK wrote:

> 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?

Issue 1: Business Rule

The question of whether you use "null" or "not null" for a column really depends on your business rule for that column.

If the business rule states,

      "A value for this column is optional.  The user
       doesn't have to enter one."

then this column is a good candidate for null.

A table cell which is "null" means that the user has not inserted anything into that cell. The user did not put any value into the cell, not even a space, not even a 0-length string, not even a zero.

If you need to maintain a distinction between nothing and a 0-length string, blank or zero, then your column should be null.

Issue 2: Space Hog?

A null column is not a space hog. Space hogs are fixed length char fields like char(200) which usually contain far less the 200 bytes. Make char(200) columns varchar(200). This will save gobs of space.

Issue 3: Client Developers

Many client developers have an innate fear of null. The practical use of the null set is foreign to many of them. However, the client developers must learn the null set. It is crucial in Sybase. As for you, the DBA, do not prohibit the use of null under any circumstance. It is too valuable a feature. In many cases, it becomes a required feature.

Issue 4: CT Library Client Programmers

CT Library programmers will have to pass the address of the null indicator when calling ct_bind. They really should be doing this anyway. Many, I have a feeling, will blow this argument off and just look what's in result memory whether its valid or not. I have seen that before.

Issue 5: Intense Updates & Null

If you have an program which is updating a table rapidly (100+ tps), then a null column will slow the updates down a lot. The null column can cause updates not to occur "in-place" but rather be deferred. If this is your case, then try not to use null. But remember the business rule will taken priority.

---

Brian Ceccarelli                 bceccarelli_at_hotmail.com
Information Systems Consultant
Metro Information Services                   

Raleigh, NC 783-8887

Current Client:

........................................................ 
Administrative Computing Services         (919) 513-1160 
Hillsborough Building, B17-A        nsysbnc_at_acs.ncsu.edu

North Carolina State University
Raleigh, NC 27695

---

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