Does it make any difference (in DB size) if I set the text field size to less than 255 (for example 55 only) in Microsoft Access? Doing so, Can I save some bytes if I have enough amount of repeating data in that text field?
Does it make any difference (in DB size) if I set the text field size to less than 255 (for example 55 only) in Microsoft Access? Doing so, Can I save some bytes if I have enough amount of repeating data in that text field?
There is *no* penalty for declaring 255. Access will only use what you put in there. It does not reserve the space.
Not to hijack the thread but I've been wondering about this: does access reserve space for other data types that are left blank, nulls? For strings what if the value *was* 255 chars and the user changed it to something shorter, is the extra space still used? Does a compact/repair come in to play here?
Access does reserve for some data types: numeric fields for example. Nulls are simply undefined so what is to reserve? I believe the data engine deals with the other issues properly but I don't know the exact answers.
This is what I meant, if I have a field of numeric datatype, not required, and the user simply never inputs a number into that field, does the database still reserve space for that numeric even though it's null at the moment? It just seems like if it didn't then that could lead to fragmentation issues, but I know next to nothing of what I'm talking about so that's why I ask.
I'm sorry but I don't know the answer to your question. Is that subject causing you some sort of issue? If so, can you describe it for us?
Nope, just trying to get a feel for how it works under the hood. I thought the questions correlated well with the original post. Better understanding can only lead to better design, right?
You could always experiment - create a db with 100k records, then add a numeric field and see if the db increases in size.
Thanks for the suggestion.
I created a table with an ID autonumber field, a string field f1 and a numeric (byte) field f2. Used vba to populate the table with 100,000 rows. After each step below I ran a compact & repair before I recorded the file size.
The initial database, with no values in f2 was 3,792,896 bytes
Updating the f2 field of every record in the table to 128: 3,780,608 bytes (less than initial ??)
Updated the f2 field of every record in the table to random: 3,780,608 bytes
Deleted f2 entirely, then added f3 currency datatype but didn't add any data: 4,083,712 bytes
Changed f3 to integer datatype: 3,883,008 bytes
So it would appear that the database does reserve space for numeric datatypes even if they are null.
--EDIT--
I did a little further experimenting in regard to text fields per OP.
Created a table with an ID autonumber field and a string field f1, max length 255. created 100k rows.
I initialized the f2 fields all with 26 chars, database size: 5,419,008 bytes
I updated all f2 field to each contain 254 chars: 28,368,896 bytes
I updated all f2 fields to 1 char (PRE compact & repair): 28,401,664 bytes
And after compact & repair: 2,768,896 bytes
So it would appear that if a user edits a value of a text field that the database will still use up the space it took to hold the extra chars from before until one runs a compact/repair.
don't forget indexes will also add to file size - and text indexes are considerable larger than numeric.
Thanks for the experiments. The results are as I would have expected.