Changing Locking Schemes

alter table supports changing from any locking scheme to any other locking scheme.

You can change:
- From allpages to datapages or vice versa
- From allpages to datarows or vice versa
- From datapages to datarows or vice versa
Before you change from allpages locking to a data-only locking scheme, or vice versa, use sp_dboption to set the database option select into/bulkcopy/pllsort to true, then run checkpoint in the database if any of the tables are partitioned and the sorts for the indexes require a parallel sort.
After changing the locking scheme from allpages-locking to data-only locking or vice versa, you cannot use the dump transaction command to back up the transaction log; you must first perform a full database dump.
When you use alter table...lock to change the locking scheme for a table from allpages locking to data-only locking or vice versa, the SAP ASE server makes a copy of the table’s data pages. There must be enough room on the segment where the table resides for a complete copy of the data pages. There must be space on the segment where the indexes reside to rebuild the indexes.

Clustered indexes for data-only-locked tables have a leaf level above the data pages. If you are altering a table with a clustered index from allpages-locking to data-only-locking, the resulting clustered index requires more space. The additional space required depends on the size of the index keys.

Use sp_spaceused to determine how much space is currently occupied by the table, and use sp_helpsegment to see the space available to store the table.
When you change the locking scheme for a table from allpages locking to datapages locking or vice versa, the space management properties are applied to the tables, as the data rows are copied, and to the indexes, as they are re-created. When you change from one data-only locking scheme to another, the data pages are not copied, and the space management properties are not applied.
If a table is partitioned, changing the locking scheme performs a partition-to-partition copy of the rows. It does not balance the data on the partitions during the copy.
When you change the locking scheme for a table, the alter table...lock command acquires an exclusive lock on the table until the command completes.
When you use alter table...lock to change from datapages locking to datarows locking, the command does not copy data pages or rebuild indexes. It updates only system tables.
Changing the locking scheme while other users are active on the system may have the following effects on user activity:
- Query plans in the procedure cache that access the table are recompiled the next time they are run.
- Active multistatement procedures that use the table are recompiled before continuing with the next step.
- Ad hoc batch transactions that use the table are terminated.
  
  Warning! Changing the locking scheme for a table while a bulk-copy operation is active can cause table corruption. Bulk copy operates by first obtaining information about the table and does not hold a lock between the time it reads the table information and the time it starts sending rows, leaving a small window of time for an alter table...lock command to start.