Re: Space required for an empty varchar field?

From: Harag (haragREMOVETHESECAPITALS_at_softhome.net)
Date: 08/29/04 

Date: Sun, 29 Aug 2004 11:04:02 +0100

On Sat, 28 Aug 2004 23:17:46 +0200, Hugo Kornelis
<hugo@pe_NO_rFact.in_SPAM_fo> wrote:

>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

Thanks Hugo.

In the above table layout if MemNotes was the only varchar (nullable)
then would you add it to the original table? and make it one table.

Al.

Relevant Pages

  • Re: Difficult SQL Statment
    ... >LEFT OUTER JOIN POOL_LIEFERDAT l ... I'm not sure where dateXY comes from. ... Hugo Kornelis, SQL Server MVP ...
    (comp.databases.ms-sqlserver)
  • Re: Second opinion for a query
    ... LEFT OUTER JOIN dbo.tblImage ... You should judge by yourself if you think it's better. ... back to this query in a year or so from now. ... There are a lot of people claiming that NULLs should be avoided, ...
    (microsoft.public.sqlserver.programming)
  • Re: Difference between = and IN
    ... I misunderstood what you meant in the previous post. ... strange results if Nulls were present in the records whereas NOT ... "Hugo Kornelis" wrote: ... > Hi Andy, ...
    (microsoft.public.sqlserver.mseq)
  • RE: [Info-ingres] Outer join problem
    ... Subject: [Info-ingres] Outer join problem ... Because that first comparison t1.id=t2.bank_id is not comparing NULLS. ...
    (comp.databases.ingres)
  • RE: [Info-ingres] Outer join problem
    ... As you are outer joining an outer join there is the potential for NULLS ... If table tmp1 is non empty then the SELECT of the form ... should be nonzero no matter what the conditions cond1, cond2 are. ...
    (comp.databases.ingres)