Use the alter table command to change the structure of an existing table. You can:
Add columns and constraints
Change column default values
Add NULL and NOT NULL columns
Drop columns and constraints
Change the 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,” and 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 use the same fillfactor).
Adaptive
Server performs 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.
