Question about field size (2 Viewers)

[sjs94704]

Access defaults field size to 255. Can anyoneplease shed some light on the importance of this property and how to set it right? What happens if I don't change it?

For example, a field for the 2 letter code for STATE does not need 255 charcters! Is there a benefit to changing this property?

 

[LarryE]

Nothing happens if you don't change it. Change it to 2 characters if you wish. Or don't. I have no idea if changing it will affect the file size but I somehow doubt it.

 

[theDBguy]

I believe Access will only occupy enough space for the characters you actually use.

 

[The_Doc_Man]

OK, a quick techie overview about field sizes...

All fields are stored pretty much in the order they are defined. So in theory, the first field you name is stored first, the second field you name is stored next to it, and so on. Numeric fields (yes/no, integer, real, date, currency) have a fixed size and are stored directly in the record using the number of bytes needed to hold the maximum possible number for the given field type. So if you have a LONG integer, you need 4 bytes to store +/- 2 billion. If you have a DATE, you use 8 bytes to store dates from 1/1/0100 to 12/31/9999 and times from 00:00:00 to 23:59:59 in one combined slot. Yes/no fields store the minimum size of 1 byte. Each numeric field's contribution to the internal record length is well known.

For short-text and long-text fields, the story is a little different. For text fields, the field size defines the MAXIMUM number of bytes that can be stored in the field. If you try to store more than that, you get a constraint violation. It is OK to store fewer than the maximum number of characters, and the field can even be empty (a zero-length string).

In the stored record, a string field has a fixed-size pointer structure at the position of the field. For short-text fields the pointer identifies a spot following the fixed-size part of the record and also shows how many bytes were ACTUALLY stored. So the contribution to the size of the record is the size of the defining structure for each string PLUS the number of bytes actually stored for that string in that record. For long-text fields, the pointer structure is bigger than for short-text strings because long-text strings are stored separately from the record to which they belong and is not necessarily stored in the same area as the owning record.

The story doesn't end there, though. For forms and reports, particularly those built by one of the wizards, the string field size defines how much space to allocate on-screen or on-print for that field. For import operations, that field size identifies truncation points if importing from some source that might have more information than you wanted to keep. For export operations, the field size identifies how much space to build into the string that will represent the external record.

File size will not change based on whether you change the maximum size of a field. It will change only based on how you occupy the field.

 

[LarryE]

See? I knew it was easy.

 

[isladogs]

I beg to disagree.
I had a UK postcodes datafile which was about 1.6 GB when downloaded from an official UK government website and using default values for datatypes. After reviewing the contents of each field, i was able to reduce the file size by over 300 MB without any loss of data.
This was done by reducing text field sizes to what was actually needed and changing number datatypes from e.g. double to single, integer to byte, long integer to integer where that was possible for the various fields.
In my case, the effort was worthwhile due to the size of the file. However, I agree that in many cases there will be little or no benefit.

BTW the default short text field size can be changed in Access options. I use 50 as the default as that was the value used in many Access versions.

 

[ebs17]

As far as I know, for text, Access dynamically only occupies the memory that is required for the entered characters:

10 bytes for managing the string + 2 bytes per character (for Unicode)

So the setting of 255 is good and not a disadvantage.

 

[The_Doc_Man]

Addendum: Changing the size of a text field is important and advantageous in a specific case: If that field is indexed. There is a limit to the size of a compound index and each member field of the compound index adds up. The index dies when the sum of its maximum field sizes would exceed the limit, which is I think 255 bytes. Compressing a field size from 255 to 2 (as in 2-letter state code) is a massive advantage for indexing. It would also be an advantage for performance of indexes.

@isladogs - unless your reduction of fields included reduction of indexed text fields, I would think that MOST of your overall file size reduction came from reduction of numeric field sizes because those are always allocated fully. Strings are (so far as I know) never allocated fully, but only allocated as needed. Reducing non-indexed text field constraints would be unlikely to do anything.

If I'm wrong, I'm wrong - but I don't know of anything except indexes that would ever affect storage based on the MAXIMUM size of a string. Now usage? Yes, max size could affect that. But Access is always "sparse" in its storage algorithms.

 

[ebs17]

There is a limit to the size of a compound index and each member field of the compound index adds up. The index dies when the sum of its maximum field sizes would exceed the limit, which is I think 255 bytes

This is outside of the written Access specification. There is a limit of 10 participating fields in a composite index.

In practice, one will rather combine number fields than "novel" text fields.

 

[isladogs]

I agree that most of the reduction was due to changing to the minimum viable number datatypes
The Postcodes table is huge with over 2.6 million records & 50+ fields most of which are short text.
The table design is largely that of the downloaded datafile though I've made a few tweaks in terms of field names
It is certainly not a standard table

The table also has many indexes - mainly on number fields but also on certain text fields such as PostcodeArea, PostcodeDistrict etc:

Probably the main advantage of limiting text fields size is as a method of helping to help prevent users from entering incorrect data e.g. using a space at the start. The Postcode field can never be greater than 8 characters (including a space), PostcodeArea is 2 characters max etc.

@sjs94704
This is where you can change the default values if you wish to do so

 

[The_Doc_Man]

This is outside of the written Access specification. There is a limit of 10 participating fields in a composite index.

In practice, one will rather combine number fields than "novel" text fields.

Yep, got my databases confused. But it was an honest error - 'cause nearly everything else string-related is limited to 255 bytes.

 

[Pat Hartman]

This was done by reducing text field sizes to what was actually needed and changing number datatypes from e.g. double to single, integer to byte, long integer to integer where that was possible for the various fields.

- Numeric data types are fixed and always take the same amount of space.
- Short text fields are variable length so as far as the table goes, the specified size is the maximum allowed but the actual size occupied is the overhead + the actual contents. Where the wastage comes in with short text is with indexes. In that case, the index is fixed in width and so if you allow 50 characters for the state code, the index entry will take up 50 characters for that field.
- Long text fields are also variable in length. But a fixed portion is stored in the defining table but the text part (the contents) is stored in a separate table.

 

[isladogs]

Yes I am aware of all that but it was very clearly explained

Perhaps my only quibble is with the word 'wastage' which whilst technically correct could be seen as implying that indexes are a bad thing due to the extra space they take up. I know that's not what you meant, but wanted to make it clear to the OP that whilst indexes require extra space, they make a significant difference in speed for fields used in searches and filters

 

[Pat Hartman]

You are correct. I was not implying that the use of indexes is wasteful. I was saying that you needed to tighten up your data definitions for text data types in order to not waste space in the index since that can bloat the database and also slow down searches since the larger the space occupied by the index, the more bytes the database engine needs to move around to read through when it has to perform functions that need to read the rows in an index..

 

[isladogs]

Yes. Indeed, that was my point when I started this exchange back in post #6.
I needed the indexes in order to search this huge table efficiently but I also needed to significantly reduce the space occupied by the table

 

[sjs94704]

WOW! Hey, everyone! What a lively conversation this has turned out to be and a very, very informative one as well!

Thanks so much to EVERYONE for your insights on this topic and it helps a lot! I want you all to know that YES! I did take the time to read everyone's replies here and while I am not well versed on the more technical side of programming this all makes logical sense to me!

BTW, thanks for writing your answers in mostly plain English and not a lot of techie jargon except where you needed to that can often get confusing and overwhelming!

 

[Cotswold]

In the olden days dBASE tables with fields of eight or multiples of eight tended to be faster for indexing or filtering. I have continued to stick to that with key fields being 8 or 16 etc in length. Never using 4, 6 or 12. Plus longer fields if in the analysis, 67 is the longest ever needed for the contents then I'll make it 72 long. I'll have a 40 long field rather than 34. etc. Maybe it doesn't make any difference today but I like to think it does.

 

[gemma-the-husky]

It seems to me it must make a difference somewhere if you store a state abbreviation as size 2, or as default size 50

If you store it as 50, but only use 2, how much space does the record use. So if you use CA for California, but then rename it to California in full, how does the string store the extra 8 characters, unless it has preallocated the entire 50 characters. I don't know, just wondering.

 

[CJ_London]

So if you use CA for California, but then rename it to California in full, how does the string store the extra 8 characters, unless it has preallocated the entire 50 characters. I don't know, just wondering.

it rewrites the record (which is one way they get into a random order) and modifies the index if indexed - some debate whether that impacts the size of the index.

If it does, my question would be if a field is specified as 6 chars and indexed - then the field size is changed to 200 chars (no change to data), does that involve recreating the entire index?

 

[ebs17]

I don't know, just wondering.

Question about field size

Access defaults field size to 255. Can anyoneplease shed some light on the importance of this property and how to set it right? What happens if I don't change it? For example, a field for the 2 letter code for STATE does not need 255 charcters! Is there a benefit to changing this property?

www.access-programmers.co.uk