Memo Field Space Allocation

[CindiN]

Is it true that when you choose a Memo Data Type, that the system actually saves 64,000 characters worth of space, even if you don't use 64,000 characters.

If so, does it save this amount of space for the entire field, or for each record within that field?

Thanks!
CindiN

 

[RickSpr]

I'm not 100% sure of this, but I believe this is how it works:

Memo fields, OLE object fields, and Long Binary fields are types of "long data". (Long Binary fields are not available to us as Access users, but Microsoft uses them to hold the definitions of forms, reports, modules, etc.) Long data is stored in a separate area associated with the table, not in the actual row data. Instead, what's stored in the row data is a Long that gives the position of the real value within the table's "long data" area.

The values stored in the "long data" area are each preceded by a length field containing the current length of the value. Just enough space is allocated to hold the value and its length field. The long data area contains these length-value pairs crammed together one after another.

When you change the value of a long data field, one of two things happens. If the new value is the same size as, or shorter than, the old value, it is written on top of the old value. But if it's longer, it can't be written on top of the old value, because chances are there's another long data value (for another field, or for the same field in another row) immediately following the old value. Instead, the new, longer, value (and its length field, of course) are written at the end of the long data area, and the position stored in the table row is changed. The space occupied by the old value is abandoned, and can't be recovered until the database is compacted.

When the long data area runs out of space, it is extended, which usually makes the database grow.

As I said, I'm not certain this is how it works. But if you've programmed in other languages, you may have heard of a "heap" type of space allocation. Heap allocation is efficient for data of highly variable and unpredictable length. (Windows, in fact, uses a modified form of heap allocation to manage your hard disk space, which contains files of highly variable and unpredictable length.) I have assumed that Access uses a heap allocation technique for its long data fields, and experiments I've done are consistent with that assumption. Besides that, some kind of heap allocation is the only known way of managing variable length data without allocating the maximum possible length, so I believe I'm on solid ground here. Rick Sprague