Table Storage Requirements
To estimate the bytes of disk space a table will occupy you must consider overhead as well as space occupied by data. You must also have a realistic idea of the maximum number of records to be stored in the table.
Formulas for estimating space requirements for tables are described in this topic. The formulas use the following variables:
| E | Estimated table storage required (bytes) |
| I | Estimated index storage required (bytes) |
| P | Physical record length (maximum 32,767 bytes) |
| N | Number of records in the table (estimate) |
| T | Table overhead (88 bytes per table) |
| F | Field overhead (86 bytes per field) |
| R | Record overhead (2 bytes per record) |
| Di | Data storage requirement for field i per record
(Depends on data type. See the following table.) |
| Oi | Overhead for field i per record (2 bytes)
(While the number of bytes for data overhead is given as 2 for all data types, character fields of odd length actually require only 1 byte of overhead per record.) |
| f | Number of fields in table |
The formula for calculating the physical record length, P, is:
The general formula for estimating bytes of storage required by a table is:
Substituting for P yields the formula:
Use the following table to find the data storage (D) and overhead (O) for the i-th field, depending on the data type.
The maximum physical record length is 32,767, which includes overhead. When determining the length of long records, use the formula for P, given earlier.
VARCHAR and LONG VARCHAR data types are provided for ANSI and DB2 compatibility only. Data for these types is currently stored as regular character data. All of the BLOB data types (image, binary, varbinary and long varbinary) are stored in separate files-- one file per blob column.
| Data Type | D | O |
|---|---|---|
| smallint | 2 | 2 |
| int | 4 | 2 |
| float 4 | 4 | 2 |
| float 8 | 8 | 2 |
| money | 8 | 2 |
| decimal | (precision/2)+1 | 2 |
| date | 6 | 2 |
| time | 6 | 2 |
| char (odd length) | length | 1 |
| char (even length) | length | 2 |
| varchar (odd length) | length | 1 |
| varchar (even length) | length | 2 |
| long varchar | 4056 | 2 |
| graphic (odd length) | length | 1 |
| graphic (even length) | length | 2 |
| vargraphic (odd length) | length | 1 |
| vargraphic (even length) | length | 2 |
| long vargraphic | 4056 | 2 |
| blob (image, binary, varbinary or long varbinary) | stored in separate file; up to 2GB each | 4 |