These 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," 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 to determine the average data row size:
4 + (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:
2016 / (Average data row size) = Number of data rows per page
In "Step 3: Calculate the Size of Clustered Index Rows," you must perform the addition twice. The first time, calculate the maximum index row size, using the given formula. The second time, calculate the average index row size, substituting the sum of the average number of bytes in the variable-length index keys for the sum of the defined number of bytes in the variable-length index keys:
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:
(2016 - (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," you must perform the addition twice. The first time, calculate the maximum leaf index row size, using the given formula. The second time, calculate the average leaf index row size, substituting the average number of bytes in the variable-length index keys for the sum of bytes in the variable-length index keys:
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 = Average 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:
2016 / (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:
(2016 - (2 * Maximum index row size)) / Average index row size = Number of nonleaf index rows per page