Estimating the Size of a Table Without a Clustered Index

Estimating the Size of a Table Without a Clustered Index

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 does not have a clustered index:

1. Calculate the space used to store data.
   
   
2. Calculate the space used to store each additional nonclustered index.
   
   
3. Sum the values calculated.

For each calculation, specify the number of rows that will be present in the table. The number of rows in the table will have a direct effect on the size of the table:

Number of rows in the table = Num_Rows

Calculate the Space Used to Store Data

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

Calculate the Space Used to Store Each Additional Nonclustered Index

The followings steps can be used to estimate the size of a single nonclustered index on a table that does not have a clustered index:

1. If the index definition includes fixed-length and variable-length columns, calculate the space each of these groups of columns occupies within the index row. The size of a column depends on the data type and length specification. For more information, see Data Types.

   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

2. If there are fixed-length columns in the index, a portion of the index row is reserved for the null bitmap. Calculate its 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.

3. If there are variable-length columns in the index, determine how much space is used to store the columns within the index row:

   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.

4. Calculate the index row size:

   Total index row size (Index_Row_Size) = Fixed_Key_Size + Variable_Key_Size + Index_Null_Bitmap + 1 + 8

5. Calculate the number of index rows per page (8096 free bytes per page):

   Number of 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.

6. Calculate the number of reserved free index rows per leaf page, based on the fill factor specified for the nonclustered index. For more information, see Fill Factor.

   Number of free index rows per leaf 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.

7. Calculate the number of pages required to store all the index rows at each level of the index:

   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 / Index_Rows_Per_Page

   Repeat the second calculation, dividing the number of pages calculated from the previous level n by 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 index level:

   Number of pages (level 2) (Num_Pages_Level_2) = Num_Pages_Level_1 / 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

8. Calculate the size of the index (8192 total bytes per page):

   Nonclustered index size (bytes) = 8192 x Num_Index_Pages

Calculate the Size of the Table

Calculate the size of the table:

Total table size (bytes) = Data_Space_Used +  Nonclustered index size + ...n

See Also

Creating an Index

Nonclustered Indexes