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 for information about
this option.
Adaptive Server performs a data copy when:
You drop a column.
You modify any of these properties of a column:
The datatype (except when you increase the length of varchar, varbinary, or 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 a 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.
You 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.
You 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.
You change the user-defined datatype ID but the physical datatype does not change. For example, if your site has two datatypes mychar1 and mychar2 that have different user-defined datatypes but the same physical datatype, data copy does not happen if you change mychar1 to mychar2.
You change the 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.
