The max_rows_per_page value specified in a create table, create index, or alter table command restricts the number of rows allowed on a data page, a clustered index leaf page, or a nonclustered index leaf page. This reduces lock contention and improves concurrency for frequently accessed tables.
max_rows_per_page applies to the data pages of a heap table or the leaf pages of an index. Unlike fillfactor, which is not maintained after creating a table or index, Adaptive Server retains the max_rows_per_page value when adding or deleting rows.
The following command creates the sales table and limits the maximum rows per page to four:
create table sales
(stor_id char(4) not null,
ord_num varchar(20) not null,
date datetime not null)
with max_rows_per_page = 4
If you create a table with a max_rows_per_page value, and then create a clustered index on the table without specifying max_rows_per_page, the clustered index inherits the max_rows_per_page value from the create table statement. Creating a clustered index with max_rows_per_page changes the value for the table’s data pages.
