Size of memo fields

[David Cleave]

Hi all

Can anyone tell me if memo fields are of a fixed size or
if each item of data stored has a different size depending
on its contents? I am currently using 255 character text
fields, but these are sometimes not big enough. I'm
thinking of changing them to memo fields, but I don't want
my database to suddenly balloon in size.

If memo fields do vary in size according to their
contents, what is the point in having the smaller text
data type?

Thank you for your help

David

 

[Douglas J. Steele]

Each item of data stored has a different size (which is actually how text
fields are stored as well)

Having said that, though, you will see an increase in size, although I doubt
it'll be that dramatic. Rather than storing the text as part of each row,
what's stored is a 10 byte pointer to another location in the file where the
memo field is stored.

 

[Tim Ferguson]

If memo fields do vary in size according to their
contents, what is the point in having the smaller text
data type?

Memos do indeed take up as much memory as needed to hold the contents --
plus a significant overhead. Then again, TEXT fields only take the storage
that they require, so that 10 bytes in a TEXT(255) is the same as in a
TEXT(10), but both are noticeably smaller, faster and probably safer than
the equivalent in a memo.

Functional advantages of using text fields of the right size include:

Indexing (which does use the full width of the text field, and you can't
index a memo field);

Sorting, grouping and selecting (because of ditto);

Hard to track bugs related to overall record size limits -- you can
define ten TEXT(255) fields which will be fine until the first time someone
enters more than 1024 chars in total; and

Validation -- define a Title field for Mr, Mrs, Miss etc as Text(255)
and sooner or later someone will enter "Mr Richard" into it.

There's probably others, but it's (3) and (4) that do it for me.

Hope that helps


Tim F

 

[John Vinson]

Hi all

Can anyone tell me if memo fields are of a fixed size or
if each item of data stored has a different size depending
on its contents? I am currently using 255 character text
fields, but these are sometimes not big enough. I'm
thinking of changing them to memo fields, but I don't want
my database to suddenly balloon in size.

Neither Text nor Memo fields are fixed size, nor do they store
trailing blanks.

Text fields differ from Memo fields in that they can be Indexed,
allowing for (sometimes very much) faster searching; you can sort data
by a Text field but not by a memo field; you can include Text fields
in a UNION query but not memo fields - and text fields are far less
likely to cause database corruption than memo fields are. By all means
use memo fields when necessary - but try to keep them to a minimum.

 

[TC]

Memos do indeed take up as much memory as needed to hold the contents --
plus a significant overhead. Then again, TEXT fields only take the storage
that they require,

plus a 1-bit indicator for null/notnull

TC


so that 10 bytes in a TEXT(255) is the same as in a