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

SQL Server - ALTER Keyword



ALTER TABLE

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



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



ALTER COLUMN

The SQL Server (Transact-SQL) ALTER COLUMN keyword is used to change the data type of a column in an existing table.



Syntax

The syntax for using ALTER COLUMN statement to modify datatype of a column in SQL Server (Transact-SQL) is given below:

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

In this table, the datatype of City column is VARCHAR(100). To change the datatype of the column to VARCHAR(255), the SQL Server code 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.


❮ SQL Server Keywords