Data Copying

SAP ASE 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, SAP ASE rebuilds the indexes when the data copy finishes.

Note: 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.

SAP ASE 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, SAP ASE 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). SAP ASE 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 SAP ASE 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.