Adaptive Server performs a data copy only if it must temporarily copy data out of a table before it changes the table’s schema. If the table has any indexes, Adaptive Server rebuilds the indexes when the data copy finishes.
If alter table is performing a data
copy, the database that contains the table must have select
into/bulkcopy/pllsort turned on. See
the Reference Manual: Commands.
Adaptive Server performs a data copy when you:
Drop a column.
Modify any of these properties of a column:
The datatype (except when you increase the length of varchar, varbinary, NULL char, or NULL binary columns).
From NULL to NOT NULL, or vice-versa.
Decrease length. If you decrease a column’s length, you may not know beforehand if all the data will fit in the reduced column length. For example, if you decrease au_lname to varchar(30), it may contain a name that requires a varchar(35). When you decrease a column’s data length, Adaptive Server first performs a data copy to ensure that the change in the column length is successful.
Increase the length of a number column (for example, from tinyint to int). Adaptive Server performs data copying in case one row has a NOT NULL value for this column.
Add a NOT NULL column.
alter table does not perform a data copy when you change the:
Length of either a varchar or a varbinary column.
User-defined datatype ID, but not the physical datatype. For example, if your site has two datatypes mychar1 and mychar2 that have different user-defined datatypes but the same physical datatype, there is no data copy performed if you change mychar1 to mychar2.
NULL default value of a variable-length column from NOT NULL to NULL.
To identify if alter table performs a data copy:
Set showplan on to report whether Adaptive Server will perform a data copy.
Set noexec on to ensure that no work will be performed.
Perform the alter table command if no data copy is required; only catalog updates are performed to reflect the changes made by the alter table command.
