Use the alter table command to change the structure of an existing table. You can:
Add columns and constraints
Change column default values
Add either null or non-null columns
Drop columns and constraints
Change locking scheme
Partition or unpartition tables
Convert column datatypes
Convert the null default value of existing columns
Increase or decrease column length
You can also change a table’s partitioning attributes. See Chapter 10, “Partitioning Tables and Indexes,” for syntax and usage information. See the Reference Manual: Commands.
For example, by default, the au_lname column of the authors table uses a varchar(50) datatype. To alter the au_lname to use a varchar(60), enter:
alter table authors modify au_lname varchar(60)
You cannot use a variable as the argument to a default
that is part of an alter table statement.
Dropping, modifying, and adding non-null columns may perform a data copy, which has implications for required space and the locking scheme. See “Data copying”.
The modified table’s page chains inherits the table’s current configuration options (for example, if fillfactor is set to 50 percent, the new pages have this same fillfactor).
Adaptive
Server does partial logging (of page allocations) for alter
table operations. However, because alter table is
performed as a transaction, you cannot dump the transaction log
after running alter table; you must dump the database
to ensure it is recoverable. If the server encounters any problems during
the alter table operation, Adaptive Server rolls back the transaction.
alter table acquires an exclusive table lock while it is modifying the table schema. This lock is released as soon as the command has finished.
alter table does not fire any triggers.
