Does Access store its data in fixed length or variable length rows

[Dennis]

--
Dennis

 

[Clifford Bass via AccessMonster.com]

Hi,

I would assume variable length. Of course it may not even store all of
the the data for a particular row in a contiguous section of the file. You
could do a quick and dirty test to see if it is variable or fixed length.
Create a new database with one table with a text column of 200 or so
characters. Create a method that lets you specify to append a thousand or so
rows of data. Make a copy of the database. Run the process on one database
telling it to save a short value, say a single character. In the other run
the process on the second telling it to save a long value, say 200 characters.
Close and compact both. Compare their sizes. What do you get?

Clifford Bass

 

[Tony Toews [MVP]]

I would assume variable length. Of course it may not even store all of
the the data for a particular row in a contiguous section of the file.

With the exception of Memo and OLE fields a row is always stored in
one 4K page. (2k page sizes in Jet 3.5/Access 97 and earlier) You
can test this yourself by created a table with about 20 text fields
set to 255 character length. You will be able to save slightly less
than 4096 characters. But once you hit the limit you will get an
appropriate error message.

Of course multiple records can be stored in one 4K page if there is
sufficient room.

You
could do a quick and dirty test to see if it is variable or fixed length.
Create a new database with one table with a text column of 200 or so
characters. Create a method that lets you specify to append a thousand or so
rows of data. Make a copy of the database. Run the process on one database
telling it to save a short value, say a single character. In the other run
the process on the second telling it to save a long value, say 200 characters.
Close and compact both. Compare their sizes. What do you get?

Given that Jet extends the Access files in 4K chunks this might be not
very precise.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/

 

[Clifford Bass via AccessMonster.com]

Hi Tony,

But that does not answer the question of how it stores the data within a
row. Not counting UNICODE compression, does it use the same amount of disk
space when the fields of a particular row contain little data as when the
fields of another row contain a lot of data. My suggested experiment I have
just done and the answer is that it appears to store the data in variable
length rows.

tblLarge
F01 Text(255)
...
F30 Text(255)

All thirty fields have UNICODE compression turned off. No indexes on
the table. Two copies of the same database. Same code in each database to
populate the table with 2000 rows. In one the code was run to populate the
columns of each of the rows with random 10-character data. In the other with
random 66-character data. (Attempting to populate all the columns with 67 or
more characters each causes the "Record too large." error.) Databases
decompiled and compacted. Respective sizes: 1,636KB and 8,296KB.

Clifford Bass

I would assume variable length. Of course it may not even store all of
the the data for a particular row in a contiguous section of the file.

With the exception of Memo and OLE fields a row is always stored in
one 4K page. (2k page sizes in Jet 3.5/Access 97 and earlier) You
can test this yourself by created a table with about 20 text fields
set to 255 character length. You will be able to save slightly less
than 4096 characters. But once you hit the limit you will get an
appropriate error message.

Of course multiple records can be stored in one 4K page if there is
sufficient room.

You
could do a quick and dirty test to see if it is variable or fixed length.

[quoted text clipped - 4 lines]

the process on the second telling it to save a long value, say 200 characters.
Close and compact both. Compare their sizes. What do you get?

Given that Jet extends the Access files in 4K chunks this might be not
very precise.

Tony

 

[Tony Toews [MVP]]

But that does not answer the question of how it stores the data within a
row.

It is variable length.

Databases
decompiled and compacted. Respective sizes: 1,636KB and 8,296KB.

Yup, that would be large enough that the 4 kb wouldn't be significant.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/