470,644 Members | 1,202 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,644 developers. It's quick & easy.

NULL values and Indexes

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
Jul 23 '05 #1
3 11994
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.
Jul 23 '05 #2

"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

Jul 23 '05 #3
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
--

Jul 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

26 posts views Thread by Agoston Bejo | last post: by
reply views Thread by Steven Hilton | last post: by
19 posts views Thread by Baldur Norddahl | last post: by
15 posts views Thread by deko | last post: by
9 posts views Thread by John Sidney-Woollett | last post: by
13 posts views Thread by Federico Balbi | last post: by
reply views Thread by warner | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.