In SQL Server, the ALTER TABLE statement is used to modify an existing table's structure. One of the common operations performed with the ALTER TABLE command is adding a new column to an existing table. This allows you to extend the schema of your database and store additional data.

Syntax

The syntax for adding a column to an existing table in SQL Server is as follows:

ALTER TABLE table_name
ADD column_name data_type [column_constraint];

Let's break down the components of this syntax:

ALTER TABLE: Specifies that you want to modify an existing table.
table_name: The name of the table to which you want to add the column.
ADD: Indicates that you want to add a new column.
column_name: The name of the new column you want to add.
data_type: The data type of the new column, such as VARCHAR, INT, DATE, etc.
column_constraint (optional): Any constraints or attributes you want to apply to the new column, such as NOT NULL or DEFAULT values.

Examples

Here are a few examples to illustrate how to use the ALTER TABLE ADD COLUMN command:

Example 1: Adding a basic column.

ALTER TABLE employees
ADD email VARCHAR(100);

This adds a new column named "email" of type VARCHAR with a maximum length of 100 characters to the "employees" table.

Example 2: Adding a column with a NOT NULL constraint.

ALTER TABLE customers
ADD phone_number VARCHAR(15) NOT NULL;

This adds a new column named "phone_number" of type VARCHAR with a maximum length of 15 characters to the "customers" table. The column is set as NOT NULL, meaning it must have a value for each row.

Example 3: Adding a column with a default value.

ALTER TABLE orders
ADD order_status VARCHAR(20) DEFAULT 'pending';

This adds a new column named "order_status" of type VARCHAR with a maximum length of 20 characters to the "orders" table. The column is assigned a default value of 'pending'. If a value is not explicitly provided for this column during an INSERT operation, it will default to 'pending'.

Remember, when adding a column to an existing table, you should consider the impact on the existing data and any related queries or applications.