Calculating storage requirements for disk-based tables
The general formula for the space required for a disk-based table is:
chkpt_factor x (table_size + sum_of(index_size))
where:
▪ chkpt_factor is between 1.0 and 3.0
▪ table_size = 1.4 x rows x (sum_of(col_size + 1) + 12)
▪ rows is the number of rows
▪ sum_of(col_size + 1) is the sum of the sizes of the columns plus one byte per column. For column sizes, see Maximum sizes of column types.
▪ For each disk-based index, the index_size is 1.4 x rows x (pkey_size + idx_size), where pkey_size is the sum of the sizes of the columns in the primary key, and idx_size is the sum of the sizes of the columns in the index.
chkpt_factor is used to reflect that checkpoint operations can briefly require up to three times the size of the database.
During a checkpoint operation, a copy of each of the changed pages in the database is copied from memory to disk. If every page in the database has been updated, then it might be necessary to copy as many pages from memory as there already are on disk. Furthermore, the most recent successful checkpoint is not deleted until the current checkpoint is successfully completed. Therefore, during a checkpoint the disk can simultaneously have up to 3 copies of each page (1 copy for the page in the database, 1 copy in the most recent successful checkpoint, and 1 copy for the current checkpoint while it is executing). The checkpoint factor therefore can be between 1.0 and 3.0. Values approaching 3.0 are rare in most databases. A value of 1.5 is usually sufficient even for small databases that have high levels of activity. The less frequent the checkpoint, the larger the chkpt_factor might need to be.
Note In a disk-based index, if you do not explicitly define a primary key, the server uses a server-generated row number (ROWID) as the primary key. This forces the primary key index to store records in the same order that they were inserted.