Table Text Field Size Implications

[thebigthing313]

Hello all,

If I know for a fact that a text field will always be, say 20 characters long, is there a benefit to setting the field size to 20 instead of the default 255? Especially for indices and index searches?

Thanks in advance.

[JoeM]

I like to do it (limit it to 20) to help prevent bad data from being entered.
It also helps keep the database size down (which is a good reason to do it!).

[thebigthing313]

My personal preference is to control bad data entry on the form-level.

As for database size, is this true? I was under the impression that Field Size is more a constraint on the field rather than a determination of the size of the datatype.

If that isn't the case then is the size difference big enough (assume 500,000 records), to compromise the ability to increase character length in the unlikely, but still possible event that it would be needed?

I'm also more interested in the effect of text field size if it is used as a primary key and therefore indexed. Would index operations suffer a noticeable performance hit with Text(255) vs Text(20)?

[JoeM]

My personal preference is to control bad data entry on the form-level.

Always a good idea, but it will also give you an extra layer of control in case records are ever imported or someone does figure out how to open the table directly and enter them.

As for database size, is this true? I was under the impression that Field Size is more a constraint on the field rather than a determination of the size of the datatype.

Try it out and see, it isn't too hard to test this. Create two different databases. Create a table in each one, where everyone is exactly the same except the field size. Then enter the same data in each one, and them check the database sizes. I just checked a table with three fields and ten records, and noticed a difference. If you try a table of 500,000 I suspect you will see a big difference.

I could be wrong, but I believe that Access allocates so much space to memory based on the field size, which would explain the difference.

I'm also more interested in the effect of text field size if it is used as a primary key and therefore indexed. Would index operations suffer a noticeable performance hit with Text(255) vs Text(20)?

I have no idea how it might affect key fields and indexes.

But note the bigger the size of your database, the more performance issues you will have. Also note that Access database have a size limit of 2 GB.

The general rule of thumb I follow when setting up fields in Access tables is to use the smallest possible data-type and field length. For example, if you were a teacher and were tracking test grades (where they could range from 0-100), using a Long Integer data type would be overkill. Byte (0-255) would be sufficient.

Also be sure to Compact and Repair your database regularly. That will get rid of the bloat, and keep it running as lean and efficient as possible.