T-SQL Tutorial

Alter Table Add Column


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.