Re: Space required for an empty varchar field?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 08/28/04 

Date: Sat, 28 Aug 2004 23:17:46 +0200

On Sat, 28 Aug 2004 10:56:01 +0100, Harag wrote:

>To the other gurus replying.
>
>Whats the problem of splitting the table into 2 tables?
>
>TABLE 1
>memID int identify<pk>
>memName varchar(30)
>MemDOB smalldatetime
>... etc
>
>
>TABLE 2
>MemID int <pk>
>MemNotes varchar(500)
>
>
>
>then in the SELECTS left join the 2 tables together on MemID where the
>notes will be needed? table 2 would only hold the rows where there are
>actual notes.
>
>Al.

Hi Al,

The upside: Less space lost for storing the length of the varchar column
and the NULL bit. Less problems caused by queries written by programmers
who don't understand how to deal with NULLS.

The downside: More space lost for storing MemID in another table as well.
Some queries get more complicated (need to join in one more table - this
will often have to be an outer join). Correct handling of NULLS in queries
remains a necessity, as they'll be in the result set of an outer join.

Plus, it gets unwieldy fast if you have multiplle NULLable columns. I'd
only consider it if I had multiple columns that are either all NULL or all
non-NULL, but that's just my personal opinion.

Best, Hugo 

-- 
(Remove _NO_ and _SPAM_ to get my e-mail address)