Ms-Access: any need to have a low size for variable-length text fields

views:

143

answers:

+6 Q:

Ms-Access: any need to have a low size for variable-length text fields

In an Ms-Access MDB, will it save disk space to limit the size of variable-length text fields?

eg. If I have a variable-length Text field of size 20 and all the actual values of the field are under 10 characters, am I wasting space?

Would it be better to set the size of the field to 10, or does it not make any difference?

+2 A:

The fields are stored as variable length, and thus setting the size as 255, or 10 will not reduce or change the size of the data file.

Albert D. Kallal 2010-07-21 04:15:13

I think it's a legacy property from back when memory was expensive.

You could use it as a method of data validation, i.e. if the field holds phone numbers, then giving a length of 10 will prevent invalid and international phone numbers from being entered.

PowerUser 2010-07-21 15:44:22

@Poweruser: wouldn't the UI be validating input?

Craig Johnston 2010-07-22 03:27:43

While one would be wise to avail oneself of whatever tools the UI provides for validating user input, the database engine itself should enforce as much as possible the entry of valid data. Choosing field length is one of the engine-level tools for doing so.

David-W-Fenton 2010-07-22 17:40:42

@David: the UI should have validated any input before it gets to the DB

Craig Johnston 2010-07-23 02:52:25

Thinking that there's no need to limit the size of the column, as your answer seems to imply, relates to another question: which comes first, the database or the application? http://stackoverflow.com/questions/3305958/database-designing-an-art-or-headache-managing-relationships

pascal 2010-07-23 12:43:46

Yes, the UI should validate all input before it reaches the database. But the database should also have validation rules and indexes that prevent invalid data. You don't know that all data entry/editing will go through the application you've created, and you can't guarantee that mistakes won't be made in the UI validation, or errors introduced in some other way. You need both belt and suspenders or you're application is weak.

David-W-Fenton 2010-07-23 20:47:34

The length of the field should be chosen appropriately because it has an effect on index performance and storage because longer values make for more data pages. Of course, if the data is real, i.e., the real values need to be that long, you need it. So, if you have a 50-character field and the longest value is 10 characters, the index won't take up any more space than if the field were 10 characters.

But choosing an appropriate limit helps with designing forms and reports -- if you make the field too long, you may end up with outlier records that don't display/print properly. On the other hand, you don't want it to be too short so that users can't fit normal data in it. Field length is the most basic form of data validation, because you are deciding what the range of appropriate values is for that field.

My basic principle is that I pick the shortest possible field length for data that has a regular format (even if it has occasional exceptions), but am generous with fields that do not.

David-W-Fenton 2010-07-21 18:05:54

@David: please elaborate on how it affects storage.

Craig Johnston 2010-07-22 03:33:59

Index storage specifically: "Wider" indexes take up more data pages and thus become fragmented more quickly, thus reducing performance. Narrower indexes are quicker to search because it's less data.

David-W-Fenton 2010-07-22 17:39:24

@David any reference? It seems strange that index space is related to the column definition, and not to the column actual usage.

pascal 2010-07-23 12:44:36

You're right, @pascal. You could define a 100-character column with an index, but if the max length was 10, the index likely wouldn't take any more space than if your column was set to 10. It would not surprise me, however, if there were not performance issues associated with having an index on a column with so much potential slack space, but I have no documentation on that, just a gut sense from working with Jet all these years. Like strong data typing, it just seems like good design to me.

David-W-Fenton 2010-07-23 20:13:40

+1 A:

Although it's not explicitly stated, I think you're asking is there a difference regarding data storage with the limit on variable length strings: There will be no difference using larger or smaller limits. From MSDN, discussing the TEXT data types for JET (The db engine in Access)

In general, text fields can be up to 255 characters, [...] In addition, unused portions of text fields are not reserved in memory.

Intermediate, Microsoft JET, MSDN

mdma 2010-07-23 17:33:45

+3 A:

If I have a variable-length Text field of size 20 and all the actual values of the field are under 10 characters, am I wasting space?

No.

Would it be better to set the size of the field to 10, or does it not make any difference?

It would be better to set 10 as the field size limit if values longer than 10 characters are unacceptable. If it's OK for a user to enter values between 11 and 20 characters, leave the limit at 20. The disk space use is a non-issue in this situation.

HansUp 2010-07-26 13:26:32

ansaurus

tags:

views:

answers:

Ms-Access: any need to have a low size for variable-length text fields

related questions