How to Use Alter Table in SQL Server

In SQL Server, the `ALTER TABLE` statement is a powerful tool that allows you to modify the structure of existing tables. Whether you need to add a new column, change the data type of an existing column, or even drop a column, `ALTER TABLE` can help you achieve your goals efficiently. This article will guide you through the process of using `ALTER TABLE` in SQL Server, providing examples and explanations to help you understand its various functionalities.

Adding a New Column

To add a new column to an existing table, you can use the `ADD` clause in the `ALTER TABLE` statement. Here’s an example:

“`sql
ALTER TABLE Employees
ADD Email VARCHAR(100);
“`

In this example, we are adding a new column named `Email` of type `VARCHAR(100)` to the `Employees` table.

Modifying a Column’s Data Type

If you need to change the data type of an existing column, you can use the `ALTER COLUMN` clause. Here’s an example:

“`sql
ALTER TABLE Employees
ALTER COLUMN Salary DECIMAL(10, 2);
“`

In this example, we are changing the data type of the `Salary` column from `INT` to `DECIMAL(10, 2)` in the `Employees` table.

Dropping a Column

To remove a column from an existing table, you can use the `DROP COLUMN` clause. Here’s an example:

“`sql
ALTER TABLE Employees
DROP COLUMN Email;
“`

In this example, we are dropping the `Email` column from the `Employees` table.

Adding Constraints

You can also use the `ALTER TABLE` statement to add constraints to existing columns. For instance, to add a primary key constraint to the `EmployeeID` column in the `Employees` table, you can use the following statement:

“`sql
ALTER TABLE Employees
ADD CONSTRAINT PK_Employees PRIMARY KEY (EmployeeID);
“`

Conclusion

The `ALTER TABLE` statement in SQL Server is a versatile tool that enables you to modify the structure of existing tables. By understanding how to use `ALTER TABLE`, you can efficiently manage your database schema and ensure that your tables meet your evolving requirements. Remember to always back up your data before making structural changes to your tables, as these changes can be irreversible.