Estimating the Size of a Table or Database
You can estimate the size of a Microsoft Jet table or database. You cannot determine the exact size of a table or database for the following reasons:
-
The size of system tables can vary greatly.
-
The estimate doesn’t account for the space taken up by indexes in your database.
-
For simplicity, the estimate assumes contiguous record storage. Microsoft Jet stores records on 2K pages and doesn’t split records across pages. Therefore, you might have unused space on individual data pages, generally less than the size of an average record.
-
The length of some fields can vary depending on the data they contain. When you estimate the size of a table that contains a variable-length field, the estimate is based on the maximum size of the field, not on the actual length of the data it contains. For example, suppose you define a variable-length field with a size of 25 characters, and you store the character Y in this field. The actual storage consumed will be 2 bytes: 1 for the character and 1 for the overhead. This is 23 bytes fewer than you might use to estimate the storage space for a table.
Consider the following when you estimate the size of a Microsoft Jet table or database:
-
Records in Microsoft Jet versions 1.0 through 2.5 databases have an overhead of 7 bytes per record, and records in version 3.0 and 3.5 databases have an overhead of 6 bytes per record.
-
Fields have an overhead of 1 byte, plus 1 byte for every 256 bytes of record storage.
-
Fixed-length fields for Yes/No, Byte, Integer, Long Integer, Single, Double, and Date/Time data types have an overhead of 1 byte per field, rounded to the next higher byte.
-
Zero-length text strings occupy 1 byte in the record (plus the overhead described in the preceding bullet).
-
For Microsoft Jet versions 1.0 through 2.5, the minimum size of a database is 65,536 bytes. These databases always grow in 32K chunks (32,768 bytes). Versions 3.0 and 3.5 databases have a minimum size of 40,960 bytes and grow in multiples of 2K.
-
A new database created in Microsoft Access 97 is 60K (61,440 bytes) because of additional system tables added by Microsoft Access.
The following table estimates the size of a table in a Microsoft Jet 3.5 database. The table has 10,000 records.
|
Field |
Data type/Overhead description |
Size in bytes |
Overhead bytes |
|
|
|
1 |
Text |
25 |
1 |
|
2 |
Text |
25 |
1 |
|
3 |
Number/Double |
8 |
1 |
|
4 |
Date/Time |
8 |
1 |
|
|
Record Overhead |
N/A |
6 |
|
|
|
Totals |
|
66 |
10 |
The total size of the table is 66 + 10, or 76, bytes per record. Because the table has 10,000 records, the total size of the table is 760,000 bytes (76 x 10,000). Because this example takes into account the maximum size of the text fields, this is only an estimate of the table size. The actual size of the values in the text fields determine the actual size of the table.
The following table estimates the size of the Customers table in the Microsoft Jet 3.5 Northwind.mdb sample database. The Customers table has 91 records.
|
Field name/Overhead description |
Data type |
Size in bytes |
Overhead bytes |
|
|
|
CustomerID |
Text |
5 |
1 |
|
CompanyName |
Text |
40 |
1 |
|
ContactName |
Text |
30 |
1 |
|
ContactTitle |
Text |
30 |
1 |
|
Address |
Text |
60 |
1 |
|
City |
Text |
15 |
1 |
|
Region |
Text |
15 |
1 |
|
PostalCode |
Text |
10 |
1 |
|
Country |
Text |
15 |
1 |
|
Phone |
Text |
24 |
1 |
|
Fax |
Text |
24 |
1 |
|
Record Overhead |
N/A |
N/A |
6 |
|
Overhead for >256 bytes of text |
N/A |
N/A |
1 |
|
|
|
Totals |
|
268 |
18 |
The total size of the Customers table is 268 + 18, or 286, bytes per record. Because the table has 91 records, the size of the table is 26,026 bytes (286 x 91). Again, because the actual sizes of the text field values aren’t known, this is only an estimate.