The followings steps can be used to estimate the amount of space required to store the data in a table.
Number of rows in the table = Num_Rows
Number of columns = Num_Cols
Sum of bytes in all fixed-length columns = Fixed_Data_Size
Number of variable-length columns = Num_Variable_Cols
Maximum size of all variable-length columns = Max_Var_Size
Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )
Only the integer portion of the above expression should be used; discard any remainder.
Total size of variable-length columns (Variable_Data_Size) = 2 + (Num_Variable_Cols x 2) + Max_Var_Size
If there are no variable-length columns, set Variable_Data_Size to 0.
This formula assumes that all variable-length columns are 100 percent full. If you anticipate that a lower percentage of the variable-length column storage space will be used, you can adjust the result by that percentage to yield a more accurate estimate of the overall table size.
Total row size (Row_Size) = Fixed_Data_Size + Variable_Data_Size + Null_Bitmap +4
The final value of 4 represents the data row header.
Number of rows per page (Rows_Per_Page) = ( 8096 ) / (Row_Size + 2)
Because rows do not span pages, the number of rows per page should be rounded down to the nearest whole row.
Number of free rows per page (Free_Rows_Per_Page) = 8096 x ((100 - Fill_Factor) / 100) / Row_Size
The fill factor used in the calculation is an integer value rather than a percentage.
Because rows do not span pages, the number of rows per page should be rounded down to the nearest whole row.
Number of pages (Num_Pages) = Num_Rows / (Rows_Per_Page - Free_Rows_Per_Page)
The number of pages estimated should be rounded up to the nearest whole page.
Table size (bytes) = 8192 x Num_Pages
Designing Tables |