How to Add a Column to a Table in SQL Server

In SQL Server, adding a column to an existing table is straightforward and can be done using the ALTER TABLE command. This allows you to modify the structure of an existing table without losing any data. In this tutorial, we will walk you through the steps to add a column to a table in SQL Server using SQL syntax.

Step 1: Understanding the ALTER TABLE Command

The ALTER TABLE statement is used to change the structure of an existing table. To add a new column to a table, the syntax is:

ALTER TABLE table_name
ADD column_name data_type;

Where:

• table_name is the name of the table where the column is being added.
• column_name is the name of the new column you want to add.
• data_type defines the type of data that the new column will store, such as INT, VARCHAR, DATE, etc.

Step 2: Example of Adding a Column

Let's say we have a table called employees, and we want to add a column named hire_date to store the date an employee was hired. Here's how you would do it:

ALTER TABLE employees
ADD hire_date DATE;

After running this query, a new column hire_date of type DATE will be added to the employees table.

Step 3: Adding Multiple Columns

If you want to add multiple columns at once, you can separate them with commas. Here's an example of adding both a phone_number and email columns:

ALTER TABLE employees
ADD phone_number VARCHAR(15),
    email VARCHAR(100);

This will add two new columns, phone_number and email, to the employees table.

Step 4: Verifying the Column Addition

To verify that the column has been successfully added, you can use the DESCRIBE command or query the INFORMATION_SCHEMA.COLUMNS table:

SELECT column_name
FROM information_schema.columns
WHERE table_name = 'employees';

This will list all columns in the employees table, including the newly added ones.

Conclusion

Adding a column in SQL Server is a simple task using the ALTER TABLE command. This tutorial covered the basic syntax for adding one or more columns to a table and how to verify the changes. Be sure to adjust the data types and constraints to match your needs when adding new columns to your tables.