Adding and Deleting Columns
You can add columns 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, the Database Engine 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. The Database Engine inserts null values into the column or returns an error if the new column does not allow null values.
Conversely, you can delete columns from existing tables, except for columns with the following characteristics:
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.
For information about adding and deleting columns from tables that are published for replication, see the "Adding Columns" and "Dropping Columns" sections of Making Schema Changes on Publication Databases.
To add or delete a column