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

Introduction

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 new column to table SQL? After all, dropping a table and starting again is not an option here. The other option is taking the backup and creating 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.

Read: SQL Career Path - Step By Step Microsoft SQL Server Career Guide

Before we start with the actual discussion “how to add new column SQL to an existing table,” 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 for creating a table in SQL. Moving ahead, we will discuss how to add new column SQL using Alter statement.

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. The syntax for SQL add new column will be discussed later.

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: You can Studio as well to create a new table and add new column to table SQL.

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 to create a new table and to add new column to table SQL?

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 the Status IS field is defined as the primary key. So, this is just a basic script that creates a small table and is 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.

Read: All you Need to Know About SQL Database Administrator Salary

When you create database objects, it is a wonderful practice to see whether 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: Job Roles and Responsibilities of a SQL Server Developer you Need to Know

DROP TABLE IF EXISTS [TaskTracker].[Status]

Add this code at the starting of the script and use the same script again. It will first drop the existing table and create 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 to add SQL new column.

It is possible to generate 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 SQL new column, 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.

Read: What is the SQL Insert Query? How to Insert (Date, Multiple Rows, and Values in Table)

How to add a new column to a table?

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

ALTER TABLE table_name  
ADD column_name column_definition;

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

ALTER TABLE employees  
ADD last_name VARCHAR(50);

Read: Learn SQL Union All Query Operators with Examples

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 the first name column.

Read: SQL Cheat Sheet With Powerful Tips & Tricks

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;

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: How to Compare MongoDB and DynamoDB?

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.

Register for a demo class now to upgrade all your SQL skills now!

How to Rename a column in an existing table?

To rename a column in SQL Server, you cannot use the ALTER TABLE statement, but you can use sp-rename command for the same. Microsoft recommends that you 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.

Read: Top 20 SSAS Interview Questions and Answers For Freshers, Experienced

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 the ALTER TABLE statement, but you can use sp-rename command for the same. Microsoft recommends that you 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.

Read: Different Type Of SQL Functions And Their Uses

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 to 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 suggestions.

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

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

Read: Most Popular SQL Server Performance Tuning Tips

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: How to Create Stored Procedure & Trigger in SQL Server

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 the 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.

Read: SQL Server Indexes-All You Need to Know

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 of 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 to join the SQL certification course at JanBask Training and improve your knowledge base right away! We just do 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!

Read: MSBI Certification & Learning Path: My Success Story You Must Know

FaceBook Twitter Google+ LinkedIn Pinterest Email