SQL – ALTER Table Statement

The ALTER TABLE statement is used to add, modify or drop columns in an existing table.

1. Add Column in an existing table:

Using ALTER TABLE statement you can add a new column in existing table.

• Add Single column:

Syntax: 
ALTER TABLE table_name
ADD column_name datatype;

Example:
Alter Table Data
Add Address varchar(100);

• Add multiple columns:

Syntax:
ALTER TABLE table_name
ADD column_1 datatype,
    column_2 datatype,
    ...
    column_n datatype;

Example:
ALTER TABLE Data
ADD firstName varchar(100),
    LastName varchar(100),
    Town varchar(50);

2. Modify column in an existing table:

You can use the ALTER TABLE statement in SQL Server to modify a column in a table.

Syntax:
ALTER TABLE table_name
ALTER COLUMN column_name datatype;

Example: 
ALTER TABLE Data
ALTER COLUMN Town varchar(100);

Earlier Town datatype was varchar(50), now it is varchar(100)

3. Drop column:

It is used to Drop a column in a table.

Syntax:
ALTER TABLE table_name
DROP COLUMN column_name;

Example:
ALTER TABLE Data
DROP COLUMN Town;

Note: Some database systems do not allow deleting a column from table.

Your valuable feedback, question, or comments about this post are always welcome or you can leave us message on our contact form , we will revert to you asap.

Recommended SQL Server Post:

SQL REPLICATE() function

SQL Server Configuration Functions

Query Optimization Technique

SQL Commands

Identity functions in SQL Server

SQL Keys