Question :
I don’t understand Step2 #8 Calculate the number of number of non-leaf pages in the index. I am not sure I understand the “Level”.
I have Num_Leaf_Pages=508
& Index_Rows_Per_Page=476
I don’t understand how they arrived at
Num_Index_Pages = 1000/(253)+ 1000/(252) + 1000/(251) = 1 + 2 + 40 = 43
“which is the number of pages described in the example.”
1000/253 does not give 1, and 1000/252 does not give 2…What am I missing with regard to the “level” and how can I plug my values of 508 and 476 in and be confident with the result.
Answer :
Attempting to implement the same, I ended up using this to approximate at a decent level of accuracy:
WHILE (@NonLeafLevels > 1)
BEGIN
DECLARE @TempIndexPages FLOAT;
-- TempIndexPages may be exceedingly small, so catch any arith overflows and call it 0
BEGIN TRY
SET @TempIndexPages = @NumLeafPages / POWER(@IndexRowsPerPage, @NonLeafLevels);
SET @NumIndexPages = @NumIndexPages + @TempIndexPages;
SET @NonLeafLevels = @NonLeafLevels - 1;
END TRY
BEGIN CATCH
SET @NonLeafLevels = @NonLeafLevels - 1;
END CATCH
END;