Storing a great many fields in a database

[Ruslan Zasukhin]

On 7/16/12 9:08 PM, "Andre Garzia" <andre at andregarzia.com> wrote:

Hi guys,

Let me explain a little LOW LEVEL ideas for VarChar and TEXT ?

1) So Peter says that SQLite always ignore length spec and store only given
chars, and Peter think that MOST OTHER dbs will eat 100 chars even if
present 1.

Peter this is wrong information (about OTHER dbs) !!
Read below. 
 
2) MOST databases, for example mySQL, Postgre and our Valentina DB have

 a) STRING(N) -- fixed length string type.
                         This one always eat 100 chars as you say

 b) VarChar(N) -- variable length string ...
                          This one eat given M chars + few helper bytes

 c) TEXT -- this differ from VarChar in the way how string is stored.
                  and  TEXT field is unlimited


You can ask self
        Why exists VarChar and TEXT ???
    
Short answer is:
    * row-based dbs as mySQL and postgre keep the whole record as single
buffer.
    * row-based dbs use PAGES to store a table.
    * even column-based db as Valentina use pages for VarChar column.

So they get obvious LIMIT on size of total record, usually at least 2
records should fit one page ...

It is know that e.g. MS SQL have 1300 bytes for total size of a record.

TEXT field, allow break this limit, because data stored outside of record
...

> Bob,
> 
> SQLite will always use TEXT and allocate just as much space as necessary to
> store the information at hand. I think it is a better option.
> 
> So if you just store 10 chars in that column, the TEXT field will use just
> that space and nothing more.
> 
> On Mon, Jul 16, 2012 at 2:58 PM, Bob Sneidar <bobs at twft.com> wrote:
> 
>> Wha?? I thought the whole point to using VARCHAR was that it only used as
>> much space as the actual text needed. What is the difference between TEXT
>> and VARCHAR then?
>> 
>> Bob
>> 
>> 
>> On Jul 16, 2012, at 9:50 AM, Peter Haworth wrote:
>> 
>>> SQLite does have some advantages over other dbs in this area.  Since it
>>> effectively ignores any length specification, e.g. VARCHAR(100), it only
>>> stores the number of characters you give it during an INSERT or UPDATE
>>> operation.  On the other hand VARCHAR(100) on most other SQL dbs will
>>> allocate enough disk space to store 100 characters even if you only put 1
>>> character in the column.  The "100" is a constraint that prevents more
>> than
>>> 100 chars from being put into the column but doesn't affect the storage
>>> space.

-- 
Best regards,

Ruslan Zasukhin
VP Engineering and New Technology
Paradigma Software, Inc

Valentina - Joining Worlds of Information
http://www.paradigmasoft.com

[I feel the need: the need for speed]