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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Does Access store its data in fixed length or variable length rows
-- Dennis |
#2
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|