}

How to add a column to an existing table in SQL Server (ALTER TABLE Statement)

Created:

Learn here how to add a column to an existing table on SQL Server.

Example:

ALTER TABLE table_1 ADD column_1 int NOT NULL DEFAULT(1) GO

The sql above will add a new column named column_1 with type int using the default value 1. Also on the query the NOT NULL constraint is added, to avoid null values on that column just created.

When using the DEFAULT statement it will use that default value in existing rows, this way the NOT NULL constraint will not be not violated.

General format of the alter table query:

ALTER
TABLE {TABLENAME} ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} CONSTRAINT
{CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE} [WITH VALUES]

Adding multiple column in the table

You can also add multiple columns in the same query:

ALTER TABLE table_name
  ADD column_1 column_definition,
      column_2 column_definition,
      ...
      column_n column_definition;
sql