Modifying locking schemes and table schema

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:

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.

Copyright © 2005. Sybase Inc. All rights reserved.

View this book as PDF