Memo Size vs. Text Size (A2K-SR1)

I have a table of 10 fields, 2 of which are memo fields. The memo fields are not used on all records. What I would like to know is: When I get up into the hundreds of thousands of records will there be any difference to the final compacted size between if I use maximum size text fields or memo fields and/or if I split them out into separate tables. Testing with only a few thousand records I cannot see any difference between the four different scenarios. Whats the real deal?

Re: Memo Size vs. Text Size (A2K-SR1)

To the best of my knowledge, a text field can be a maximum of 255 records and if you put 2 of those text fields in a table, the dbms will be saving all that space in every record, whether you have used them at all, used them to capacity or not. Potentially, a great resource waster.

Memo fields are handled differently. The dbms will, transparent to you, will create other table(s) for your memo entries, only creating a record when a memo field is used.

So it seems that a memo field will do what you want in the most efficient manner.
Someone else may have a more technical explanation.

Re: Memo Size vs. Text Size (A2K-SR1)

ThomasW, that's what I thought, but, I just received a private email that said both memo and text fields only store the data entered, regardless of the field size setting. Blank text or memo fields do not reserve space or add to the size of the DB. Separating the fields out to other tables would actually increase the size due to storing additional recordID's to connect the tables.
Can anyone confirm or refute this?

Re: Memo Size vs. Text Size (A2K-SR1)

The straight scoop is as follows:
Text fields require 1 byte for every character in the field (assuming you are not using UNICode which takes two bytes), plus a few overhead bytes for the field. So a text field with size 255 only takes that much room if it contains 250+ characters. A memo field actually always takes at least 16 bytes regardless of it size. Memo fields are actually stored on linked pages (2KB I believe) that are separate from the page the record is stored on, and can be very large. So if you had lots of records with no data, the text version would be a little bit smaller.

The decision really revolves around whether you need fields longer than 255, and whether you want to risk the add fragileness of memo fields - they do tend to corrupt more easily than text fields. Hope this helps.

Re: Memo Size vs. Text Size (A2K-SR1)

Re: Memo Size vs. Text Size (A2K-SR1)

Well, it looks like text fields it is. Do you think that splitting them out to separate tables will be any significant space savings or is it more trouble than its worth? (These fields are for comments and will be used only about 10 percent of the time.)

Re: Memo Size vs. Text Size (A2K-SR1)

If the fields are only used occasionally, then creating a separate table with a one-to-one relationship to the first would allow you to only create comments records when you needed them and to easily link them to their parent record.

Re: Memo Size vs. Text Size (A2K-SR1)

Corruption usually occurs as a result of the following:
<UL><LI>a lockup and reboot of a workstation connected to the database, or the server where the database resides crashing
<LI>a power failure which takes either a workstation or the server down
<LI>a network problem which causes a workstation to loose the connection to the database[/list]It's not entirely clear why memo fields are more susceptible, but my guess is that it is because they are stored on separate pages, and there are links to be established and maintained, which takes longer than simply writing a record into a single page. Hope this sheds a little light on things.