Calculating the Size of a 9,000,000 Row Table with a Nonclustered Index

Calculating the Size of a 9,000,000-Row Table with a Nonclustered Index

The following example calculates the size of a nonclustered index on the 9,000,000-row table used in "Calculating the Size of a 9,000,000-Row Table with a Clustered Index," earlier in this appendix. There are two keys, one fixed- and one variable-length. The table contains the following:

9,000,000 rows
Sum of bytes of fixed-length columns = 100
Sum of bytes of two variable-length columns = 50
Nonclustered key, fixed-length = 4 bytes
Variable-length, 20 bytes, 1 key

Follow these steps:

Calculate the size of the leaf index row:



9	Overhead
+ 4	Sum of length of fixed-length keys
+ 20	Sum of length of variable-length keys
+ 2	Number of variable-length keys + 1
----
35	Subtotal

35	Subtotal
+ 1	(Subtotal/256) + 1
----
36	Size of Leaf Index Row

Calculate the number of leaf pages in the index:
2016/36 = 56 Nonclustered leaf rows per page
9,000,000/56 = 160,715 Leaf pages
Calculate the size of the nonleaf rows:
36 + 4 = 40 Size of nonleaf index row
Calculate the number of nonleaf pages:
(2016/40) - 2 = 48 Nonleaf index rows per page
160,715/48 = 3348 Index pages, Level 1
3348/48 = 69 Index pages, Level 2
69/48 = 2 Index pages, Level 3
2/48 = 1 Index page, Level 4
Calculate the total number of nonleaf index pages:



Totals:	Pages	Rows
Level 4 (Root)	1	2
Level 3	2	69
Level 2	69	3,348
Level 1	3,348	60,715
Level 0 (Leaf)	160,715	9,000,000
	------
Total number of 2K pages	164,135