Use alter table to change the locking scheme for a table. The syntax is:
alter table table_name
lock {allpages | datapages | datarows}
This command changes the locking scheme for the titles table to datarows locking:
alter table titles lock datarows
alter table supports changing from one locking scheme to any other locking scheme. Changing from allpages locking to data-only locking requires you to copy the data rows to new pages and re-create any indexes on the table.
Changing the locking scheme takes several steps and requires sufficient space to make the copy of the table and indexes. The time required depends on the size of the table and the number of indexes.
If you are changing from datapages locking to datarows locking or vice versa you need not copy data pages and rebuild indexes. Switching between data-only locking schemes updates only system tables, and finishes quickly.
You
cannot use data-only locking on tables that have rows that are at,
or near, the maximum length of 1962 (including the two bytes for
the offset table).
For data-only-locked tables with only fixed-length columns, the maximum user data row size is 1960 bytes (including the 2 bytes for the offset table).
Tables with variable-length columns require 2 additional bytes for each column that is variable-length (this includes columns that allow nulls.)
See “Determining Sizes of Tables and Indexes” in Performance and Tuning Series: Physical Database Tuning for information on rows and row overhead.
