The following steps can be used to estimate the amount of space required to store the data and any additional nonclustered indexes on a table that has a clustered index.
For each calculation, specify the number of rows that will be present in the table. The number of rows in your table will have a direct effect on the size of your table.
Number of rows in the table = Num_Rows
To calculate the space used to store data, see Estimating the Size of a Table.
Note the value calculated:
Space used to store data = Data_Space_Used
The following steps can be used to estimate the amount of space required to store the clustered index.
Number of columns in index key = Num_CKey_Cols
Sum of bytes in all fixed-length key columns = Fixed_CKey_Size
Number of variable-length columns in index key = Num_Variable_CKey_Cols
Maximum size of all variable-length key columns = Max_Var_CKey_Size
Index Null Bitmap (CIndex_Null_Bitmap) = 2 + (( Num_CKey_Cols + 7) / 8 )
Only the integer portion of the above expression should be used; discard any remainder.
Total size of variable length columns (Variable_CKey_Size) = 2 + (Num_Variable_CKey_Cols x 2) + Max_Var_CKey_Size
If there are no variable-length columns, set Variable_CKey_Size to 0.
This formula assumes that all variable-length key columns are 100 percent full. If you anticipate that a lower percentage of the variable-length key column storage space will be used, you can adjust the result by that percentage to yield a more accurate estimate of the overall index size.
Total index row size (CIndex_Row_Size) = Fixed_CKey_Size + Variable_CKey_Size + CIndex_Null_Bitmap + 1 + 8
Number of index rows per page (CIndex_Rows_Per_Page) = ( 8096 ) / (CIndex_Row_Size + 2)
Because index rows do not span pages, the number of index rows per page should be rounded down to the nearest whole row.
Number of pages (level 0) (Num_Pages_CLevel_0) = (Data_Space_Used / 8192) / CIndex_Rows_Per_Page
Number of pages (level 1) (Num_Pages_CLevel_1) = Num_Pages_CLevel_0 / CIndex_Rows_Per_Page
Repeat the second calculation, dividing the number of pages calculated from the previous level n by CIndex_Rows_Per_Page until the number of pages for a given level n (Num_Pages_CLevel_n) equals one (index root page). For example, to calculate the number of pages required for the second index level:
Number of pages (level 2) (Num_Pages_CLevel_2) = Num_Pages_CLevel_1 / CIndex_Rows_Per_Page
For each level, the number of pages estimated should be rounded up to the nearest whole page.
Sum the number of pages required to store each level of the index:
Total number of pages (Num_CIndex_Pages) = Num_Pages_CLevel_0 + Num_Pages_CLevel_1 +
Num_Pages_CLevel_2 + ... + Num_Pages_CLevel_n
Clustered index size (bytes) = 8192 x Num_CIndex_Pages
The following steps can be used to estimate the amount of space required to store each additional nonclustered index.
Number of columns in index key = Num_Key_Cols
Sum of bytes in all fixed-length key columns = Fixed_Key_Size
Number of variable-length columns in index key = Num_Variable_Key_Cols
Maximum size of all variable-length key columns = Max_Var_Key_Size
Index Null Bitmap (Index_Null_Bitmap) = 2 + (( Num_Key_Cols + 7) / 8 )
Only the integer portion of the above expression should be used; discard any remainder.
Total size of variable length columns (Variable_Key_Size) = 2 + (Num_Variable_Key_Cols x 2) + Max_Var_Key_Size
If there are no variable-length columns, set Variable_Key_Size to 0.
This formula assumes that all variable-length key columns are 100 percent full. If you anticipate that a lower percentage of the variable-length key column storage space will be used, you can adjust the result by that percentage to yield a more accurate estimate of the overall index size.
Total nonleaf index row size (NL_Index_Row_Size) = Fixed_Key_Size + Variable_Key_Size + Index_Null_Bitmap + 1 + 8
Number of nonleaf index rows per page (NL_Index_Rows_Per_Page) =
( 8096 ) / (NL_Index_Row_Size + 2)
Because index rows do not span pages, the number of index rows per page should be rounded down to the nearest whole row.
Total leaf index row size (Index_Row_Size) = CIndex_Row_Size + Fixed_Key_Size + Variable_Key_Size + Index_Null_Bitmap + 1
The final value of 1 represents the index row header. CIndex_Row_Size is the total index row size for the clustered index key.
Number of leaf level index rows per page (Index_Rows_Per_Page) = ( 8096 ) / (Index_Row_Size + 2)
Because index rows do not span pages, the number of index rows per page should be rounded down to the nearest whole row.
Number of free index rows per page (Free_Index_Rows_Per_Page) = 8096 x ((100 - Fill_Factor) / 100) /
Index_Row_Size
The fill factor used in the calculation is an integer value rather than a percentage.
Because index rows do not span pages, the number of index rows per page should be rounded down to the nearest whole row.
Number of pages (level 0) (Num_Pages_Level_0) = Num_Rows / (Index_Rows_Per_Page - Free_Index_Rows_Per_Page)
Number of pages (level 1) (Num_Pages_Level_1) = Num_Pages_Level_0 / NL_Index_Rows_Per_Page
Repeat the second calculation, dividing the number of pages calculated from the previous level n by NL_Index_Rows_Per_Page until the number of pages for a given level n (Num_Pages_Level_n) equals one (root page).
For example, to calculate the number of pages required for the second and third index levels:
Number of data pages (level 2) (Num_Pages_Level_2) = Num_Pages_Level_1 / NL_Index_Rows_Per_Page
Number of data pages (level 3) (Num_Pages_Level_3) = Num_Pages_Level_2 / NL_Index_Rows_Per_Page
For each level, the number of pages estimated should be rounded up to the nearest whole page.
Sum the number of pages required to store each level of the index:
Total number of pages (Num_Index_Pages) = Num_Pages_Level_0 + Num_Pages_Level_1 +Num_Pages_Level_2 + ... + Num_Pages_Level_n
Nonclustered index size (bytes) = 8192 x Num_Index_Pages
Calculate the size of the table:
Total table size (bytes) = Data_Space_Used + Clustered index size + Nonclustered index size + ...n
Clustered Indexes | Nonclustered Indexes |
Creating an Index |