Re: A97 - what would you do 99 times out of a hundred?

Tim Marshall <TIMMY!@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
news:djgi2k$506$1@xxxxxxxxxxxxxxxxxx:

> The explanation by others in this thread who have replied (all of
> whom I greatly respect as being heads and shoulders above me in
> Jet experience, BTW) describing their use of null for an undefined
> text value sounds to me as no different as a zero length string.
> The difference being a zero length string is stored in a table's
> indexes, while a null is not.

But the indexes still return the Null values very quickly, because
they aren't *in* the index.

In my opiniont, the only reason to avoid Nulls is if you must
include your field in a unique index.

I agree that storing a default value makes a great deal of sense,
but simply setting ALLOW ZLS to True does *not* accomplish that, it
just makes it *possible* to store a ZLS in that field.

So what you're really talking about here is not just a matter of
whether or not you allow ZLS's, as was the original question, but of
whether or not you set a default value. You could have a non-ZLS
default value and disallow ZLS's (which I'd see as a good thing, as
ZLS's are incredibly hard to work with). On the other hand, if you
truly want to distinguish "Not Known" from "Has None" then the ZLS
is a good way to store the latter.

I've never had an application where that was a distinction that was
worth maintaining, but there certainly can be such situations.

But I truly think that absent *that* requirement, it's only UNIQUE
indexes that require a value. Searches for empty values in
non-unique indexes are not going to be speeded up by storing a ZLS
(or any other string).

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
.

Relevant Pages
Loading