A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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



 
 
Thread Tools Display Modes
  #1  
Old January 10th, 2010, 05:23 AM posted to microsoft.public.access.tablesdbdesign
Dennis
external usenet poster
 
Posts: 1,222
Default Does Access store its data in fixed length or variable length rows


--
Dennis
  #2  
Old January 12th, 2010, 07:50 PM posted to microsoft.public.access.tablesdbdesign
Clifford Bass via AccessMonster.com
external usenet poster
 
Posts: 150
Default Does Access store its data in fixed length or variable length rows

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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/201001/1

  #3  
Old January 13th, 2010, 11:21 PM posted to microsoft.public.access.tablesdbdesign
Tony Toews [MVP]
external usenet poster
 
Posts: 3,776
Default Does Access store its data in fixed length or variable length rows

"Clifford Bass via AccessMonster.com" u48370@uwe wrote:

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/
  #4  
Old January 14th, 2010, 12:27 AM posted to microsoft.public.access.tablesdbdesign
Clifford Bass via AccessMonster.com
external usenet poster
 
Posts: 150
Default Does Access store its data in fixed length or variable length rows

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

Tony Toews [MVP] wrote:
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


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/201001/1

  #5  
Old January 15th, 2010, 05:33 AM posted to microsoft.public.access.tablesdbdesign
Tony Toews [MVP]
external usenet poster
 
Posts: 3,776
Default Does Access store its data in fixed length or variable length rows

"Clifford Bass via AccessMonster.com" u48370@uwe wrote:

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/
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:03 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.