Expense of switching to or from allpages locking

Switching from allpages locking to data-only locking or vice versa is an expensive operation, in terms of I/O cost. The amount of time required depends on the size of the table and the number of indexes that must be re-created. Most of the cost comes from the I/O required to copy the tables and re-create the indexes. Some logging is also required.

The alter table...lock command performs the following actions when moving from allpages locking to data-only locking or from data-only locking to allpages locking:

If a clustered index exists on the table, rows are copied in clustered index key order onto the new data pages. If no clustered index exists, the rows are copied in page-chain order for an allpages-locking to data-only-locking conversion.

The entire alter table...lock command is performed as a single transaction to ensure recoverability. An exclusive table lock is held on the table for the duration of the transaction.

Switching from datapages locking to datarows locking or vice versa does not require that you copy pages or re-create indexes. It updates only the system tables. You are not required to set sp_dboption "select into/bulkcopy/pllsort".