Use the alter table command 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 copying the data rows to new pages and re-creating any indexes on the table.
The operation 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.
Changing from datapages locking to datarows locking or vice versa does not require copying data pages and rebuilding indexes. Switching between data-only locking schemes only updates system tables, and completes in a few seconds.
You
cannot use data-only locking for 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 Chapter 11, “Determining Sizes of Tables and Indexes,” in the Performance and Tuning: General Information for information on rows and row overhead.
