If alter table performs a data copy, you can also include a 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
However, you cannot use alter table to change table schema and the locking scheme of a table that has a clustered index. If a table has a clustered index you can:
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.
