Applying max_rows_per_page to existing data

There are several ways to apply a max_rows_per_page value to existing data:

• If the table has a clustered index, drop and recreate the index using a different max_rows_per_page value.

• Use sp_chgattribute to change the value of max_rows_per_page, then rebuild the entire table and its indexes with reorg rebuild. For example, to change the max_rows_per_page value of the authors table to 1, enter:

  sp_chgattribute authors, "max_rows_per_page", 1
  go
  reorg rebuild authors
  go
  

• Use bcp to repopulate the table, and:

  1. Copy out the table data.

  2. Truncate the table.

  3. Use sp_chgattribute to set the max_rows_per_page value.

  4. Copy the data back in.