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.
| Copyright © 2005. Sybase Inc. All rights reserved. |
|
|
