How to add column in SQL Server?

When working with SQL Server database, you might want to modify a current table and add a new column to it without losing existing data. The ALTER TABLE statement will help.

If you use ALTER TABLE statement, it will automatically add the new column to the end of the table. The ALTER privilege is required to perform this:

ALTER TABLE table_name ADD col_name data_type NULL | NOT NULL;

It will not drop any existing data.

For example, you can add a new column final_score to the table class:

ALTER TABLE class
  ADD final_score INT NULL;

You can also add multiple columns at once using the ALTER TABLE statement:

ALTER TABLE table_name
  ADD col_name_1 data_type_1 NULL | NOT NULL,
      col_name_2 data_type_2 NULL | NOT NULL,
      ...
      col_name_n data_type_n NULL | NOT NULL;

Let’s look at an example that shows how to add multiple columns to a table in SQL Server:

ALTER TABLE class
  ADD last_name VARCHAR(50) NOT NULL,
      final_score INT NULL;

Using TablePlus GUI Tool

TablePlus supports viewing and editing database structure without having to writing queries.

Open the database with the default table view.
Switch to the database structure editor by clicking on the structure button at the bottom, or use the shortcut keys Cmd + Ctrl + ].
Click on + Column to create a new column, or you can double-click on an empty row from the table view.
Specify column definition: name, data type, nullability, default value, and foreign key.
Hit Cmd + S to save the changes to the server. You can also click on the Commit button near the top left of the windows.

Add column in SQL Server using TablePlus

Need a good GUI Client to work with MS SQL Server? TablePlus provides a modern, native tool with intuitive UI to manage multiple databases in cluding SQL Server, MySQL, PostgreSQL, SQLite, Oracle… And it’s available for free!