T-SQL Tutorial T-SQL Advanced Database Management T-SQL References

T-SQL - ALTER TABLE



The T-SQL (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 T-SQL (Transact-SQL) constraints on an existing table.

Syntax

The syntax for using ALTER TABLE statement to ADD, DROP and MODIFY columns in T-SQL (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 statement 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 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:

    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 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 T-SQL, see Data Types reference.