How to Add A New Column to a Table in SQL?

Every time when a table is created in SQL Server using T-SQL, you need to specify all the columns for that table with their data type and constraints. But what happens when you suddenly have to add a new column to that table? After all, dropping a table and starting again is not an option here. The other option is taking the backup and create it again after dropping the existing table.

But these options are not optimum, and they cannot be considered good choices for programmers. The answer to this problem is ALTER TABLE statement in SQL that is used to add, modify, or delete columns in the existing table. It can also be used to add or drop various constraints on the existing table.

Before we start with the actual discussion “how to add a new column to an existing table in SQL,” let us first learn how to create a table in SQL Server using a query. To create a table in SQL Server Management Studio, you may use the following steps:

• In the SQL Server Management Studio, click the “New Query” button.
• Here, you have to type a “Create Table” script.
• Now, click on “!Execute” option from the toolbar.

Let us see a quick example for your reference:

Creating a table in the SQL involves the name of a table, the definition of columns and its associated data types too. The CREATE TABLE Query in SQL is used to create a new table. The basic syntax for creating a new table in SQL is given below –

CREATE TABLE new_table_name

(

first_column first_column_data_type,

second_column second_column_data_type,

third_column third_column_data_type,

....

last_column last_column_data_type

);

The “CREATE TABLE” Script

Here is a SQL script for the above example in SQL Server Management Studio:

Create table status (

statusid int indentity(1,1) not null,

statusname  varchar(50) not null,

Datecreated datetime not null constraint Df_Status_DateCreated DEFAULT (Getdate()),

constraint pk_Status Primary Key clustered (statusid)

)

So, how does this script work?

It will create a table. It adds three columns and defines data types for each column. It specifies that Status ID is an identity column, and the value of the first column is 1. The value for each subsequent column will automatically increase by 1. It specifies that value in the Status Name column can have a maximum of 50 characters.

It specifies that NULL values are not allowed for the column. In the Date Created Column, it sets a default value for the current data and Status IS field is defined as the primary key. So, this is just a basic script that creates a small table and quite easy to understand. You can quickly customize this script to run the whole database with its objects, data, and permission all in one go.

When you create database objects, it is a wonderful practice to see either an object already exists or not. It prevents the error and helps in managing redundancy or duplicate entries that ultimately helps in improving the overall performance of a database script. Keep in mind that Create table script will work only if the table does not exist in the database. Here is how the script will change in this case.

IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='Status' AND xtype='U')

You should add this code to your script at the upper end and run the script again. You can also replace an existing table too. For this purpose, you should drop the existing table first and create a new one then. For example:

Read: How to Restore a Database Backup from SQL Server Management Studio

DROP TABLE IF EXISTS [TaskTracker].[Status]

Add this code in the starting of the script and use the same script again. It will first drop the existing table and creates a new one as per the requirement. Now, you have learned how to create a table and a table script in SQL Server Management Studio. Moving ahead, let us discuss how to generate a script from an existing table.

It is possible generating a SQL Script from Object Explorer in the SSMS GUI. With this, SQL generates all the SQL code from the table and creates a table. For this purpose, you just have to right-click on the table and create a script for the same. It is not limited to tables only, but you can generate scripts for database objects as well.

SQL ALTER TABLE Statement

ALTER TABLE statement in SQL is used to add, modify, or delete columns in the existing table. It can also be used to add or drop various constraints on the existing table. Let us see the concept of the ALTER TABLE statement in detail below.

How to add a new column to a table?

The Alter Statement in SQL is used to add a new column to the existing table without dropping or removing it. Here is the basic syntax for the same:

ALTER TABLE table_name  
ADD column_name column_definition;

Take an example of an employee table, where you want to add a new “LAST NAME” column later. It can be done through the ALTER TABLE statement. Here is an example for your reference.

ALTER TABLE employees  
ADD last_name VARCHAR(50);

How to add multiple new columns to the existing table?

With the ALTER TABLE statement, it is not necessary to add only one column at a particular time, but you are free to add multiple columns too. Here is the syntax of how to add multiple new columns to an existing table.

ALTER TABLE table_name  
ADD column_1 column_definition,      
column_2 column_definition,      
...      
column_n column_definition;

For example, you want to add two columns “LASTNAME” and “FIRSTNAME” to the employee table. In this case, the above basic syntax can be written as:

ALTER TABLE employees  
ADD last_name VARCHAR(50),      
first_name VARCHAR(40);

With this syntax, you can add two columns successfully where the limit is set 50 characters for the last name column and 40 characters for first name column.

How to modify a column in an existing table?

To modify a table in SQL Server, you should use ALTER TABLE statement and here is the syntax for the same:

ALTER TABLE table_name  
ALTER COLUMN column_name column_type;

For example, you want to redefine data type and its limit for a particular column. Also, you want to set the column to NOT NULL constraint. Here is how can you do it.

ALTER TABLE employees  
ALTER COLUMN last_name VARCHAR(75) NOT NULL;

It has modified the last name column to VARCHAR data type; its limit is set to 75 characters and NOT NULL constraint is also defined for the column that was not added earlier.

How to drop a column in an existing table?

To drop a table in SQL Server, you should use ALTER TABLE statement and here is the syntax for the same:

Read: Database Query Statements - Types & Uses

ALTER TABLE table_name  
DROP COLUMN column_name;

Let us understand the concept with the help of an example. It will drop an already added column that is not needed anymore.

ALTER TABLE employees  
DROP COLUMN last_name;

With the syntax, the Last Name column will be dropped from the table employee, and you cannot use it anymore.

How to Rename a column in an existing table?

To rename a column in SQL Server, you cannot use ALTER TABLE statement, but you can use sp-rename command for the same. Microsoft recommends that drop a table and create it again so that stored procedures and scripts are not broken. Here is the syntax for the same:

sp_rename 'table_name.old_column_name', 'new_column_name', 'COLUMN';

Let us understand the concept with the help of an example. It will rename an already added column to make it more meaningful.

sp_rename 'employees.last_name', 'lname', 'COLUMN';

The example clearly shows that the name of the “LAST NAME” is changed to “lname” successfully. In the same way, you can rename a table, let us see how to do it.

How to Rename an existing table?

To rename a table in SQL Server, you cannot use ALTER TABLE statement, but you can use sp-rename command for the same. Microsoft recommends that drop a table and create it again so that stored procedures and scripts are not broken. Here is the syntax for the same:

sp_rename 'old_table_name', 'new_table_name';

Let us understand the concept with the help of an example. It will rename a table according to the context as needed.

sp_rename 'employees', 'emps';

In this example, the name of the “employees” table is changed to “emps” successfully.

What are the Limitations and Restrictions?

With the ALTER TABLE statement, columns are automatically added to the end of a table. To add columns in a specific order, you should SQL Server Management Studio. However, it is not the recommended database design practice still you can use it if ordering columns are necessary for some database. It is always a pleasant appearance looking at the columns in a specific order as you want. You need the ALTER permissions to make changes into an existing table. Let us see how SQL Server Management Studio can help you to deal with the problem.

• In Object-Explorer, right-click on the table to which you want to add new columns in a specific order.
• Click the first blank cell in the “Column Name” column.
• Type the name for the column in the cell. The column name is the required value here.
• In the next step, press the TAB button to jump the DATA TYPE cell and select a data type for the dropdown menu.
• The default value is assigned here if you don’t give any suggestion.

Note: It is possible to change Options dialog box under “Database Tools”.

• Continue defining more properties for the column in the column properties tab.

NOTE: Default properties are defined for each column as soon as it is created. However, you are always free to customize a column in SQL as per the requirement.

• Once you have added all required columns, Click the Save option in the end.

Here is one more option to change the column positions, if you don’t want to use SQL SERVER Management studio to resolve the issue. When you are unhappy with the current column position, you can use the following syntax to change the column position as you want it.

Read: The Database as Part of the System Architecture

ALTER TABLE table_name 
MODIFY column_name column_definition AFTER column_name;

You can also see a practical example for a better understanding of the concept. It is given below for your reference:

mysql> ALTER TABLE shippers ADD fax VARCHAR(20) AFTER shipper_name;

The discussion is not complete if you are not sure of adding constraints to existing columns. It is possible to achieve with ALTER Table statement again. Let us see how to do it.

How to add constraints with the ALTER TABLE statement?

Here is one quick example of how to add a UNIQUE constraint to a particular column with ALTER TABLE statement in SQL.

ALTER TABLE table_name ADD UNIQUE (column_name,...);

Here is one quick example of how to add a PRIMARY KEY constraint to a particular column with ALTER TABLE statement in SQL.

ALTER TABLE table_name ADD PRIMARY KEY (column_name,...);

Here is one quick example of how to add a NOT NULL constraint to a particular column with ALTER TABLE statement in SQL.

ALTER TABLE table_name MODIFY column_name datatype NOT NULL;

Here is one quick example of how to add a CHECK constraint to a particular column with ALTER TABLE statement in SQL.

ALTER TABLE table_name ADD CONSTRAINT MyUniqueConstraint CHECK (CONDITION);

Here is one quick example of how to add a DROP constraint to a particular column with ALTER TABLE statement in SQL.

ALTER TABLE table_name
DROP CONSTRAINT MyUniqueConstraint;

Here is one quick example of how to change the data type of a particular column with ALTER TABLE statement in SQL.

ALTER TABLE table_name ALTER COLUMN column_name new_data_type;

Final Words:

Now you have the complete idea of the topic how to add a new column and play with it using ALTER TABLE statement in SQL. However, it is pretty easier to play with the ALTER TABLE command if you have the SQL background. Otherwise, you are recommended joining the SQL certification course at JanBask Training and improve your knowledge base right away! We just not deliver the training but makes you a valuable resource to get hired by top Companies and change your career graph like never before. All the Best!