Altering Metadata
Go Up to Working with Data Definition Statements
Most changes to data definitions are made at the table level, and involve adding new columns to a table, or dropping obsolete columns from it. SQL provides ALTER TABLE
to add new columns to a table and to drop existing columns. A single ALTER TABLE
can carry out a single operation, or both operations. Direct metadata operations such as ALTER TABLE increment the metadata version. Any table (and its triggers) can be modified up to a maximum of 255 times before you must back up and restore the database.
Making changes to views and indexes always requires two separate statements:
- Drop the existing definition.
- Create a new definition.
If current metadata cannot be dropped, replacement definitions cannot be added. Dropping metadata can fail for the following reasons:
- The person attempting to drop metadata is not the metadata creator.
- SQL integrity constraints are defined for the metadata and referenced in other metadata.
- The metadata is used in another view, trigger, or computed column.
For more information about dropping metadata, see Dropping Metadata.