SQL Server Tutorial SQL Server Advanced SQL Server Database SQL Server References

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:

EmpIDNameCityAge
1JohnLondon25
2MarryNew York24
3JoParis27
4KimAmsterdam30

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

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

    This will produce the result as shown below:

    EmpIDNameCityAgeSalary
    1JohnLondon25
    2MarryNew York24
    3JoParis27
    4KimAmsterdam30
  • Drop a column: To drop a column named Salary in the original Employee table, the query is mentioned below:

    ALTER TABLE Employee
    DROP COLUMN Age;
    

    This will produce the below mentioned result:

    EmpIDNameCity
    1JohnLondon
    2MarryNew York
    3JoParis
    4KimAmsterdam
  • 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 query 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.


5