SQL Server - ALTER TABLE

The SQL Server (Transact-SQL) ALTER TABLE statement is used to add, delete or modify columns in an existing table. It is also used to add or drop SQL Server (Transact-SQL) constraints on an existing table.

Syntax

The syntax for using ALTER TABLE statement to ADD, DROP and MODIFY columns in SQL Server (Transact-SQL) are given below:

/* ADD Column */
ALTER TABLE table_name
ADD column_name datatype;

/* DROP Column */
ALTER TABLE table_name
DROP COLUMN column_name;

/* MODIFY Column */
ALTER TABLE table_name
ALTER COLUMN column_name datatype;

Example:

Consider a database table called Employee with the following records:

• Add a column: To add a column named Salary in the Employee table, the statement is given below:

  ALTER TABLE Employee
  ADD Salary DECIMAL(18,2);
  

  
  

  This will produce the result as shown below:

  EmpID	Name	City	Age	Salary
  1	John	London	25
  2	Marry	New York	24
  3	Jo	Paris	27
  4	Kim	Amsterdam	30

• Drop a column: To drop the Salary column from the Employee table, the following statement can be used:

  ALTER TABLE Employee
  DROP COLUMN Age;
  

  
  

  This will produce the below mentioned result:

  EmpID	Name	City
  1	John	London
  2	Marry	New York
  3	Jo	Paris
  4	Kim	Amsterdam

• Modify a column: In the original Employee table, the datatype of City column is VARCHAR(100). To change the datatype of the column to VARCHAR(255), the statement is given below:

  ALTER TABLE Employee
  ALTER COLUMN City VARCHAR(255);
  

  
  

  This will change the datatype of City column from VARCHAR(100) to VARCHAR(255). For a complete reference of all the data types available in SQL Server database, see Data Types reference.