Adding and Deleting Columns

Microsoft® SQL Server™ 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 will insert null values into the column or return 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 table’s full-text key.

To add or delete a column

To copy columns from one table to another