How to Add Column to a Table in SQL Server?

As an SQL Server data analyst, I recently received a requirement to add a few columns to an existing table. We can achieve this using the ALTER command. I have provided a few examples of how to add single and multiple columns (with different data types) to a table in SQL Server.

I will also show, how to add a column to a table using the SQL Server management studio.

Table of Contents

ALTER TABLE statement in SQL Server

We use the Alter Table statement to add a column to an existing table in SQL Server.

The ALTER TABLE statement in SQL Server is used to modify the structure of an existing table. Here are a few uses that we can use through the ALTER Table statement.

Add a column
Add multiple columns
Rename a column
Drop a column
Add BIT column with default
Add Identity to existing column column

Note: Whatever column name we add to the existing table, it will be added at the end. We cannot add the column name in between.

Add Single Column to Existing Table in SQL Server

To add a single column in SQL to an existing table, you can use the ALTER TABLE command and insert the column name. Below is the syntax.

ALTER TABLE table_name

ADD column_name data_type;

Here, we added the column name Location to the existing table EmployeeDetail.

ALTER TABLE EmployeeDetail
ADD Location char(20);

The output shows that the above code adds a new column, Location, to the table and keeps the value NULL because we haven’t added the values for it yet.

Add Multiple Columns to Existing Table in SQL Server

With the same ALTER TABLE command, we can add multiple columns to an existing table through a single ALTER TABLE command. Here’s the syntax for adding columns in SQL.

ALTER TABLE table_name
ADD columname1 data_type,
columname2 data_type,
columname3 data_type;

For the EmployeeDetail table, I have added multiple columns Gender and Joiningdate.

ALTER Table EmployeeDetail
ADD Gender char(20),
        Joiningdate Datetime;

Now, multiple columns are added to the table in SQL Server.

Add BIT Column Type with Default Value

The BIT column type is a True / False datatype that holds values of 0 or 1 only. In the front-end application, this can be interpreted as TRUE / FALSE or YES/NO.

Below is an example of how a new Column with BIT data type and default value is added to the existing ‘EmployeeDetail’ table. Here PreferWFO is a column name with the default value 1 which means Yes.

ALTER TABLE EmployeeDetail
ADD  PreferWFO BIT NOT NULL  DEFAULT (1)

By doing this, column value will be added to the table in SQL Server by default.

Add Identity to the Existing Column in SQL Table

Here, we cannot add an identity to the existing column. However, we can add the identity while creating a new table, and the result will be displayed sequentially.

We have to drop a column in the table and then recreate it. Here, I’m deleting the salary column and adding it using identity.

ALTER TABLE EmployeeDetail
DROP COLUMN Employeesalary

Look into the query and output below. I added the identity keyword for the salary, which starts at 60000 and increments by 10000 in each column.

ALTER TABLE EmployeeDetail
ADD Employeesalary INT IDENTITY(60000, 10000) NOT NULL;

Add Date Column to the Table in SQL

Now, we will see how to add a date column to the table in the SQL server.

ALTER TABLE EmployeeDetail
Add Date DATETIME NOT NULL Default (GETDATE());

Add date column to the table in SQL Server

By doing this, the date column will be added by default to all the column values.

Add Number to the Column in SQL Server

We use the Alter Table statement to add a Phonenumber column to an existing table in SQL Server. The query is below.

ALTER TABLE  EmployeeDetail
Add Phonenumber  Varchar (12);

Add Column in SQL Server Management Studio

Before, we saw how to add a column through SQL Query; now, we will see how to add a column through SSMS.

Search your table name in Object Explorer and expand it. Click Tables-> Right click your table name -> Design.

Add column in SQL Server Management Studio

Add the required column name and data type and click File-> Save table name.

The SQL server management studio will add the new column to the SQL table.

Conclusion

This article covered all the information you need to add columns to SQL, including a basic example. Using the ALTER TABLE command, you have seen how to add, remove, and alter a column in an already-existing table. Now, you can expand your database tables by adding columns using the ALTER TABLE command.