Jump to content
Larry Ullman's Book Forums

Newsletter #70: Indexing Columns With Null


Recommended Posts

It's bad for performance. Why that is is to technical for me to delve into, but my professor used an hour explaining why during a lesson some years ago. I took the hint...

 

The other reason is because allowing null values is often an indicator of bad db design. If you have columns with lots of null values, you should probably normalize it differently. The way to do it is to add another table and join the data in those cases.

 

An example:

Person ( id, firstName, middleName, lastName, ....)

 

The middleName column will obviosly have a lot of null values, because not everyone has a middle name. A way to solve it is to break it up:

 

Person (id, firstName, lastName, ....)

MiddleName (id, middleName)

 

When you query, you simply join it:

SELECT p.*, m.middleName 
FROM Person as p
LEFT OUTER JOIN MiddleName AS m ON (p.id = m.id)
Link to comment
Share on other sites

 Share

×
×
  • Create New...