This SQL tutorial focuses on the SQL Server ALTER statement. This tutorial is the second part of two posts describing DDL (Data Definition Language) statements in SQL Server.

In this post you will learn how to modify the columns within the table (such as removing, adding, and changing the data type of a column). The previous post described the concept of creating and deleting tables.


 

SQL Server ALTER TABLE – Adding New Column

The syntax to add a column in SQL Server is:

ALTER TABLE table_name
ADD column_name column_datatype

For example:

ALTER TABLE emps
ADD emp_manager DECIMAL(3)
  • You cannot define where a new column is located; each newly inserted column is always located last.
    If the table already contains columns with values, the new column will be populated with NULL values for all these rows.

SQL Server ALTER TABLE – Changing Column Data Type

The syntax for changing a column’s data type in SQL Server is:

ALTER TABLE table_name
ALTER COLUMN column_name column_datatype

For example:

ALTER TABLE emps
ALTER COLUMN emp_manager DECIMAL(8,2)

 

SQL Server ALTER TABLE – Dropping a Column

The syntax to remove a column in SQL Server is:

ALTER TABLE table_name
DROP COLUMN column_name

For example:

ALTER TABLE emps
DROP COLUMN emp_manager