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:
Copies all rows in the table to new data pages, formatting rows according to the new format. If you are changing to data-only locking, any data rows of less than 10 bytes are padded to 10 bytes during this step. If you are changing to allpages locking from data-only locking, extra padding is stripped from rows of less than 10 bytes.
Drops and re-creates all indexes on the table.
Deletes the old set of table pages.
Updates the system tables to indicate the new locking scheme.
Updates a counter maintained for the table, to cause the recompilation of query plans.
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".
