NULL values and Indexes

[serge]

Someone was explaining me the following:

1- NULL values is NOT good for a column that has an index
2- Therefore, all columns have default values of :
- 0 for numbers
- " " for strings

I honestly can't believe that it's a good idea to have all table columns
be filled with 1-2 space bar characters in order not to leave them
as NULL values.

Am i the only one who is confused about this approach? Is the
person doing this correct or wrong? And not just the columns that
have indexes on, all the columns are being set to ALLOW NULLS and
all of them are set with Default Values of (' ') for text fields and (0)
for
number fields.
Thank you

[Michael Gray]

On Fri, 1 Apr 2005 22:57:23 -0500, "serge" <se****@nospam.ehmail.com>
wrote:

Someone was explaining me the following:

1- NULL values is NOT good for a column that has an index
2- Therefore, all columns have default values of :
- 0 for numbers
- " " for strings

I honestly can't believe that it's a good idea to have all table columns
be filled with 1-2 space bar characters in order not to leave them
as NULL values.

Am i the only one who is confused about this approach? Is the
person doing this correct or wrong? And not just the columns that
have indexes on, all the columns are being set to ALLOW NULLS and
all of them are set with Default Values of (' ') for text fields and (0)
for
number fields.
Thank you

Time to be blunt here...

You are absolutely right.
You should not be confused at all.

NULL values can be very useful, especially on indexes, as an
indication of "index is not set" or "no index here", or "default
inherited index applies".
I use Null values extensively (in huge database systems) with not
only no problems whatsoever, but measurably signifcant advantages.
People who try to tell you that "Nulls are the work of the devil", or
:the sky will fall down if you allow nulls", or some such
unsubstantiated childish delusion are exclusively ignorant of the
correct ways to handle them.
(Or too lazy/ineducable to learn their correct implementation and/or
benefits.)

Fact, just plain indisputable fact.

[Greg D. Moore \(Strider\)]

"serge" <se****@nospam.ehmail.com> wrote in message
news:EK*********************@news20.bellglobal.com ...

Someone was explaining me the following:

1- NULL values is NOT good for a column that has an index
2- Therefore, all columns have default values of :
- 0 for numbers
- " " for strings

I honestly can't believe that it's a good idea to have all table columns
be filled with 1-2 space bar characters in order not to leave them
as NULL values.
Disk space is cheap, so this is not much of an objection.

Am i the only one who is confused about this approach? Is the
person doing this correct or wrong? And not just the columns that
have indexes on, all the columns are being set to ALLOW NULLS and
all of them are set with Default Values of (' ') for text fields and (0)
for
number fields.
Ultimately the concern is cardinality. If you have lots of NULLs the index
is less useful than an index with fewer NULLs.
Of course if you have lots of 0's or single spaces,the index is less useful.



Thank you

[David Portas]

I don't think it's reasonable to imply that the ideas of people such as
Chris Date are based on ignorance or laziness. There are intelligent
arguments on both sides of the NULL debate.

Serge might want to refer to the works of Date, Darwen and Pascal for
contrary points-of-view on the problems with NULLs.

http://www.amazon.com/exec/obidos/tg...260307-5456631
http://www.dbdebunk.com/publications.html

As for saving a few bytes of storage, of all the reasons to use or not
to use NULLs that's probably the least important in my view.

--
David Portas
SQL Server MVP
--