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

Drop the index.
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).
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.