Re: NOT NULL

Doug Bernhardt wrote:
>
> Minh Giang wrote:
>
> Hi,
> How much performance degradation to a table with all fields
> defined with
> NOT NULL compare to a table with one or two fields defined with NOT
> NULL?
>
> TIA
> --
> Minh Giang
> mpg_at_fast.net
>
> Any character columns defined as NULL SQL Server will automatically
> store as varchar. Although varchars can save space, they have a
> performance impact because of the work needed to maintain these columns
> in the table as well as any indexes these columns are in.

I don't understand this last comment.

Recent NG discussion has demonstrated that null columns occupy *at most* one byte in storage.

Single-column indexes do not store references to null values at all. Where indexes do contain references to null columns there is *less* overhead associated with the reference than with any other (non-null) column reference.

The principal advantage of nulls is their usefulness in distinguishing between a zero or blank (space) value and an unknown or non-applicable attribute. This, of course, is why primary (or identifying) keys cannot contain nulls.

The chief (and necessary) disadvantage of using nulls is the fact that nulls are not regarded as equivalent to each other, so that comparisons sometimes have to be structured as:

"A = B or (A is null and B is null)".
Or, worse:
"A <> B or (A is null and B is not null) or (A is not null and B is null)".

Using the nvl() function makes this easier to write, but not more efficient in execution.
However, in these cases, the code is forcing equivalence on two null values, which may therefore be being used inappropriately.

Nulls are useful *and* efficient where appropriate, but should not be used in unjustified cases merely as a means of reducing storage.

Chrysalis. Received on Wed Mar 12 1997 - 00:00:00 CST