Alter Existing Tables

Use the alter table command to change the structure of an existing table.

You can also change a table’s partitioning attributes.

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)

Dropping, modifying, and adding non-null columns may perform a data copy, which has implications for required space and the locking scheme.

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).

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.

• Objects Using select * Do Not List Changes to Table
  If a database has any objects (stored procedures, triggers, and so on) that perform a select * on a table from which you have dropped a column, an error message lists the missing column.
• Use alter table on Remote Tables
  You can use alter table to modify a remote table using Component Integration Services (CIS).
• Add Columns
  Use the alter table command to add a column to an existing table.
• Drop Columns
  Use alter table to drop a column from an existing table.
• Modify Columns
  Use alter table to modify an existing column. You can modify any number of columns in a single alter table statement.
• Add IDENTITY Columns
  You can add IDENTITY columns only with a default value of NOT NULL. You cannot specify a default clause for a new IDENTITY column.
• Drop IDENTITY Columns
  You can drop IDENTITY columns just like any other column.
• Modify IDENTITY Columns
  You can modify the size of an IDENTITY column to increase its range. This might be necessary if either your current range is too small, or the range has been used up because of a server shutdown.
• 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.
• Modifying Locking Schemes and Table Schema
  When using alter table to modify data, you can also include the lock command to change the locking scheme of a table.
• Add, Drop, or Modify Columns with User-Defined Datatypes
  The syntax to add, drop, or modify a column to include user-defined datatypes is the same as with a system-defined datatype.
• Errors and Warnings from alter table
  Most errors you encounter when running alter table inform you of schema constructs that prevent the requested command (for example, if you try to drop a column that is part of an index).
• Rename Tables and Other Objects
  Use sp_rename to rename tables and other database objects: columns, constraints, datatypes, views, indexes, rules, defaults, procedures, and triggers.