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: How best to meet these business requirements
    ... SET STATISTICS TIME ON ... LEFT OUTER JOIN #R ... "Hugo Kornelis" wrote: ... Is there a more elegent way to do this? ...
    (microsoft.public.sqlserver.server)
  • 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: WWW/Internet 2009: 2nd CFP until 21 September x
    ... outer join. ... If you can decompose the table into multiple tables each of which has no nulls, what you discover is that a null in the combined table corresponds to an absent row in one of the decomposed tables. ... I'm with you when you suggest that experience counts and I think it's fair ball to point out where an inconsistent theory might produce consistent practical results. ...
    (comp.databases.theory)
  • Re: WWW/Internet 2009: 2nd CFP until 21 September x
    ... outer join, as he claims he thinks it does, then presumably something is ... that relation is always the join of the m projections ... that we can describe 'missing information' NULLs (not all NULLs, ... call these projections 'unnamed', 'implicit' or 'ephemeral'. ...
    (comp.databases.theory)
  • Re: WWW/Internet 2009: 2nd CFP until 21 September x
    ... outer join, as he claims he thinks it does, then presumably something is ... that relation is always the join of the m projections ... that we can describe 'missing information' NULLs (not all NULLs, ... call these projections 'unnamed', 'implicit' or 'ephemeral'. ...
    (comp.databases.theory)