The default value for max_rows_per_page is 0, which creates clustered indexes with full data pages, creates nonclustered indexes with full leaf pages, and leaves a comfortable amount of space within the index B-tree in both the clustered and nonclustered indexes.
For heap tables and clustered indexes, the range for max_rows_per_page is 0 – 256.
For nonclustered indexes, the maximum value for max_rows_per_page is the number of index rows that fit on the leaf page, without exceeding 256. To determine the maximum value, subtract 32 (the size of the page header) from the page size and divide the difference by the index key size. The following statement calculates the maximum value of max_rows_per_page for a nonclustered index:
select (@@pagesize - 32)/minlen
from sysindexes
where name = "indexname"
