Import makes every column Text 255

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there any overhead at all in leaving every text column defined as 255
characters even though the data will occupy much less space? I'm using Access
2K.
 
It doesn't really matter. Access only takes as much room as is required. 10
characters of text stored in a Text 255 field takes exactly the same amount
of storage as in a Text 10 field.
 
Actually "mscertified", there is a difference--in some cases it is
considerable. If you don't resize the text fields the database takes up more
hard drive space and it runs slower.
This, from the MS help file for the 'Field Size' field property says:
"You should use the smallest possible FieldSize property setting because
smaller data sizes can be processed faster and require less memory."

Plus, if you modify the field size the the smallest possible value and
<em>then</em> compact the database (Tools | Database Utilities | Compact and
Repair database) you will notice the database does decrease the database file
size, in some cases quite dramatically.
 
ACCESS uses only the amount of memory needed to store the field's actual
data. If the field is sized to 255 characters, but the data contain only 10
spaces, then ACCESS uses only the memory needed for the 10-character string.
ACCESS does not 'waste' space by leaving the other 245 characters empty.
 
Steve, I think the passage you quote refers to number fields. Text
fields behave as Ken describes. To prove it, do what I've just done
while watching Dr Tatiana on telly:

1) Create a new database containing a single table

Table1
ID* - Autonumber
Text1 - Text (255)

2) Create 10 records in the table, each containing a single character in
Text1.

3) Run this query

INSERT INTO Table1
SELECT A.Text1
FROM Table1 AS A, Table1 AS B,
Table1 AS C, Table1 AS D, Table1 AS E;

to append another 100,000 records.

4) Compact the database and see how big it is.

5) Change Table1 so the field size of Text1 is 1 instead of 255.

6) Compact again.
 
Although both Ken and John are correct in this, where it does matter is if
the field is indexed.

Larger than necessary, and queries can be slow to run.
 
Back
Top