Modifying Locking Schemes and Table Schema

When using alter table to modify data, you can also include the lock command to change the locking scheme of a table.

For example, to modify the au_lname column of the authors table and change the locking scheme of the table from allpages locking to datarows locking:

alter table authors
modify au_lname varchar(10)
lock datarows
  1. Drop the index.
  2. Modify the table schema and change the locking scheme in the same statement (if the change in the table schema also includes a data copy).
  3. Rebuild the clustered index.

Alternately, you can issue an alter table command to change the locking scheme, then issue another alter table command to change the table’s schema.