Add Columns to an Existing Table in SQL Server Database

Introduction

A table is a two-dimensional logical structure and the fundamental means of storing data in relational database management systems. Its ‘row and column’ format is very much like the organization of the spreadsheet. 

Each new record introduced to a table is a row (also called a record or tuple), while rows are grouped into a finite set of columns (also called fields or attributes). Each column has a name and data type, and it serves as a reference for displaying result sets when querying data from relational database systems.

In this article, we explore the current options letting us add new columns to an existing table in SQL Server database. The use case may arise on many occasions. Particularly, it happens when there are updates for an application, and they demand adding new columns.

Create a Table

To begin our short set of experiments, let’s create a small table with four columns containing basic information about some company:

-- Listing 1: Create New Table ntab
use AU
go
create table ntab (
ID INT identity (1,1)
, CompanyName varchar(100)
, CompanyAddress varchar(200)
, DateofIncorporation datetime
);

Add a Single Column

To add a single column to the ntab table, we execute the code provided in Listing 2. The result is a five-column table, as shown in Figure 1. We generated this metadata about our table with the sp_columns stored procedure.

-- Listing 2: Add ManagingDirector Column to Empty Table

alter table ntab add ManagingDirector varchar(50);

exec sp_columns ntab;

Using the code in Listing 3, we populate a new table with 1000 rows.

-- Listing 3: Add Column to Empty Table
insert into ntab values ('Simplex Technologies','Plot 121 Nova Ridge II, Appolonia City','20201023','Kenneth Igiri');
GO 1000

Then, we add another column TaxIdentityNumber – we can do so successfully with no apparent performance overhead:

-- Listing 4: Add Column to Populated Table

alter table ntab add TaxIdentityNumber varchar(20);

exec sp_columns ntab;

Drop or Add Multiple Columns using ALTER TABLE command

In a single statement, we can also drop or add columns. Important: When we drop columns from a table with data, we lose the data very quickly. It is certainly not a command to play around with in production.

The ALTER TABLE command is a Data Definition Language (DDL) command similar to TRUNCATE. The operation is not captured in the transaction log, and it is impossible to roll it back. If you have reasons to do it in production, you should have a backup so that you can fall back to the previous state.

-- Listing 5: Drop Column from Table

alter table ntab drop column ManagingDirector, TaxIdentityNumber;

alter table ntab add ManagingDirector varchar(50), TaxIdentityNumber varchar(20);

select * from ntab;

Important: When you attempt to drop a column having an index, it returns an error (see the image). First, you must back up and drop the Index.

Using the GUI – Table Designer

Before we proceed, note that you need to ensure using the latest SQL Server Management Studio version. If there is a mismatch between the database version and your SSMS version, you’ll get the following error:

To change the table structure, open the Table Designer on SSMS: right-click the table and click Design.

You will see the ntab table details:

In Table Designer, we add the columns’ names we want to add and specify the Data Types and nullability as desired.

Once done, we save the table using any of the available options. For instance, refer to the option available on the SSMS bar menu:

There is one SQL Server Management Studio setting, which may prevent saving the table with newly added columns. You may need to disable this setting:

Tools > Options > Designers> Table and Database Designers

In the background, SQL Server executes the same SQL we showed earlier (Listing 5). We extracted this using SQL Profiler. You can get the same data with Extended Events:

Once we are done, we can see the same results as before:

dbForge Studio for SQL Server

dbForge Studio for SQL Server from the Devart company offers a robust functional interface for interacting with SQL Server. As the solution is similar to SSMS, it is easy to operate. It connects seamlessly to the SQL Server instance and allows the user to smoothly interact with SQL Server objects. You can download the express edition here.

To modify a table in dbForge Studio, right-click that table to view the detailed properties.

You add a new column in the same way as you do in SQL Server’s Table Designer. However, note the detail level shown for the table, including the table DDL. It is much more robust than that available in SSMS.

dbForge Studio follows the same rules of adding columns to tables as SQL Server Management Studio. The DDL for the table is updated as soon as you add a column. However, you must click SAVE to keep the changes:

Conclusion

This article demonstrated the methods we can use to add columns to an existing table in SQL Server, whether that table has data in it or not.

We also illustrated the impact of dropping columns when the table has data. We pointed out that the ALTER TABLE command is a DDL command. There are no logs and no significant performance impact either. Use Table Designer to insert columns between existing columns rather than at the end of the column list.

We have also reviewed the Devart dbForge Studio 2019 functionality. It has a much more robust and convenient interface for this task and other similar tasks. It can also offer many other capabilities to make the SQL Server database management much easier.

References

1. Performance Impact of ALTER TABLE Commands
2. Alter Table in T-SQL
3. Table Designer Feature in dbForge Studio for SQL Server