How To Add A Column To A Table In SQL Server

Recently, my team was required to add a new column with critical data to an existing table. We used the ALTER TABLE Statement in the SQL server to add a column to the table. But before that, we ensured that we took the proper backup.

Let us discuss this with a real-time example to give you a better idea. I have an existing table named Vehicle, and it has three columns: VeicleID, VehicleName, and VehicleBrand, as shown in the screenshot below. Now, I need to add a new column, VehicleType.

Approach-1: Using ALTER TABLE Query

Case-1: Adding a single column

To add the new column, let’s execute the SQL query below. After executing this query, we got the expected output, as shown in the screenshot below.

Check out How To Create A Temp Table In SQL Server

ALTER TABLE Vehicle
ADD VehicleType nvarchar(50);

The screenshot below shows that the new column has been successfully added to the existing table, Vehicle.

Case-2: Adding multiple columns at once

You can also use the SQL query below to add multiple columns to your table.

ALTER TABLE Vehicle
ADD VehicleCost int,
    Vehicle nvarchar(50);

The above query was executed successfully, and we got the expected output, as shown below.

Check out: How To Insert Values Into Table With Identity Column In SQL Server

Case-3: Adding a column with constraints

You can also use the SQL query below to add a column to your table with a Not Null constraint.

ALTER TABLE Vehicle ADD VehicleQuality nvarchar(50) NULL
GO
UPDATE Vehicle SET VehicleQuality = ''
ALTER TABLE Vehicle ALTER COLUMN VehicleQuality nvarchar(50) not null

Now, the column with constraints is added to the table successfully, as shown in the screenshot below.

Approach-2: Using SQL Server Management Studio UI

Using the steps below, you can also use SQL Server Management Studio UI to add a single or multiple columns.

1. Right-click on the table name –> Click on the Design option.
2. Enter the column name and then select the data type. Check/select the option if you wish to allow null values and then save it. Once you click the save button, fix the Saving Changes Is Not Permitted SQL Server error if you encounter it.

Now, you can see below the column has been added successfully.

Conclusion

As discussed in this article, adding a new column to an existing table in an SQL server using the ALTER TABLE Statement is a very straightforward process. The challenge is to ensure a proper backup in case you are working on critical or production data for the safer side.

You may also like following the articles below.

• How to add identity column to existing table in SQL Server
• How to change column name in SQL Server
• How To Check SQL Server Version