The preceding calculations use the maximum size of the variable-length fields. If you know the average size of the fields, you can use this value in Steps 1 and 4 for calculating table size, and the value in Step 1 for calculating the nonclustered index size. You'll need slightly different formulas for clustered and nonclustered indexes.
In "Step 1: Calculate the Data Row Size," to determine the average data row size, use the sum of the average length of the variable-length columns instead of the sum of the defined length of the variable-length columns.
+ (sum of bytes in all fixed-length columns) + (sum of bytes of the average length of variable-length columns) = Subtotal
In "Step 2: Calculate the Number of Data Pages," use the average data row size from the first formula.
(average data row size) = number of data rows per page
In "Step 3: Calculate the Size of Clustered Index Rows," perform the addition twice.
5 + (sum of bytes in the fixed-length index keys) + (sum of bytes in variable-length keys) = Subtotal
Subtotal + ((Subtotal/256) + 1) + 2 = maximum index row size
In "Step 4: Calculate the Number of Clustered Index Pages," substitute the following formula for the first formula, using the two different length values you calculated in the previous equation.
(2,016 - (2 * maximum index row size))/average index row size = number of clustered index rows per page.
In "Step 1: Calculate the Size of the Leaf Index Row," perform the addition twice.
5 + (sum of bytes in the fixed-length index keys) + (sum of the average number of bytes in variable-length index keys) = Subtotal
Subtotal + ((Subtotal/256) + 1) + 2 = maximum index row size
In "Step 2: Calculate the Number of Leaf Pages in the Index," use the average leaf index row size in the first division procedure.
2,016/(average leaf index row size) = number of leaf rows per page
In "Step 3: Calculate the Size of the Nonleaf Rows," use the average leaf index row size.
(average leaf index row) + 4 = size of nonleaf row
In "Step 4: Calculate the Number of Nonleaf Pages," substitute the following formula for the first formula, using the maximum and average row sizes calculated in Step 1.
(2,016 - (2 * maximum index row size))/average index row size = number of nonleaf index rows per page