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
