Before you change from allpages locking to data-only locking or vice versa, Sybase® recommends that you take these steps:
If the table is partitioned, and you have not run update statistics since making major data modifications to the table, run update statistics on the table that you plan to alter. alter table...lock performs better with accurate statistics for partitioned tables.
Changing the locking scheme does not affect the distribution of data on partitions; rows in partition 1 are copied to partition 1 in the copy of the table.
Perform a database dump.
Set any space management properties that should be applied to the copy of the table or its rebuilt indexes. See “Setting Space Management Properties” in Performance and Tuning Series: Physical Database Tuning for information on rows and row overhead.
Determine if there is enough space. See “Determining the space available for maintenance activities” in Performance and Tuning Series: Physical Database Tuning .
If any of the tables in the database are partitioned and require a parallel sort:
Use sp_dboption to set the database option select into/bulkcopy/pllsort to true.
Configure for optimum parallel sort performance.
After alter table completes:
Run dbcc checktable on the table and dbcc checkalloc on the database to ensure database consistency.
Perform a database dump.
After you have changed the locking scheme from allpages
locking to data-only locking or vice versa, you cannot use dump
transaction to back up the transaction log.
You must first perform a full database dump.
