Estimate the table size (1)

Estimate the table size

The following steps can be used to estimate the amount of space required for storing data in the table.

1. Specify the number of rows in the table:

Number of rows in the table = num_rows

2. If there are fixed-length and variable-length columns in the table definition, calculate the space occupied by each of the two groups of columns in the Data row. The column size depends on the data type and length. For more information, see data types.

Number of columns = num_cols

Total Bytes in all fixed-length columns = fixed_data_size

Variable Length columns = num_variable_cols

Maximum value of all variable length columns = max_var_size

3. If a table contains a fixed-length column, a part of the row (called a vacant space chart) will be retained to manage the column's can be empty. Calculation size:

Vacancy chart (null_bitmap) = 2 + (num_cols + 7)/8)

Use only the integer part in the above expression, and remove the remaining part.

4. If the table contains variable-length columns, determine the space required to store these columns in rows:

Total Variable Length Column size (variable_data_size) = 2 + (num_variable_cols X 2) + max_var_size

If no Variable Length Column exists, set variable_data_size to 0.

This formula assumes that all variable-length columns are fully filled. If the expected ratio of storage space occupied by a variable-length column is low, you can adjust the ratio to get a more accurate estimate of the size of the entire table.

5. Calculation row size:

Total row size (row_size) = fixed_data_size + variable_data_size + null_bitmap + 4

The last value 4 indicates the first structure of the data row.

6. Next, calculate the number of rows per page (8096 available bytes per page ):

Number of rows per page (rows_per_page) = (8096)/(row_size + 2)

Because rows do not span pages, the number of rows on each page should be written down to the nearest integer.

7. To create a clustered index on a table, calculate the number of available rows on each page based on the specified fill factor. For more information, see fill factor. If you do not create a clustered index, set fill_factor to 100.

Number of available lines per page (free_rows_per_page) = 8096 X (100-fill_factor)/100)/(row_size + 2)

The fill factor used in the calculation is an integer, not a percentage.

Because rows do not span pages, the number of rows on each page should be rounded down to the nearest integer. When the fill factor increases, more data is stored on each page, so the number of pages is reduced.

8. Calculate the number of pages required to store all rows:

Page number (num_pages) = num_rows/(rows_per_page-free_rows_per_page)

The estimated page number should be rounded up to the nearest integer.

9. Finally, the amount of space required to compute the data in the storage table (the total number of bytes per page is 8192 ):

Table size (bytes) = 8192 x num_pages