Adding and Deleting Columns

Microsoft® SQL Server™ 2000 allows columns to be added to existing tables, provided that the column allows null values or a DEFAULT constraint is created on the column. When you add a new column to a table, SQL Server inserts a value in that column for each existing row of data in the table. For this reason, it is useful to add a DEFAULT definition to the column when you add it to the table. If the new column does not have a DEFAULT definition, you must specify that the new column allows null values. SQL Server inserts null values into the column or returns an error if the new column does not allow null values.

Conversely, columns can be deleted from existing tables. However, it is not possible to delete a column that is:

• Involved in replication.
  
  
• Used in an index.
  
  
• Used in a CHECK, FOREIGN KEY, UNIQUE, or PRIMARY KEY constraint.
  
  
• Associated with a DEFAULT definition, or bound to a default object.
  
  
• Bound to a rule.
  
  
• Registered for full-text support.
  
  
• Used as a full-text key for a table.

To add or delete a column

Transact-SQL

Enterprise Manager

SQL-DMO

To copy columns from one table to another

Enterprise Manager