RnewPROMO : GET UP TO 20% OFF ON LIVE CLASSES + 2 SELF-PACED COURSES FREE!! - SCHEDULE CALL Rnew

- SQL Server Blogs -

Add Column in SQL: A Complete Never-Before Guide for You in 2022



Introduction

Every time 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 column in 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. In SQL create new columns using an 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 of “how to create a new column SQL to an existing table,” let us first learn how to create a table in SQL Server using a query. To create columns in SQL, you must enhance your SQL skills. Do register yourself for SQL Training & Certification Program to give a competitive edge to your career.

Let’s dive into an enlightening journey to know about how to create a table and how to add column in SQL!

Do keep a pen & paper ready with you to note down all the steps for your future use!

Before knowing how to create column SQL, do read on steps to create a table in SQL Server Management Studio. Find the steps below.

  • In the SQL Server Management Studio, click the ‘New Query’ button.
  • Here, you have to type a ‘Create Table’ script.
  • Now, click on the ‘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 column SQL using the ALTER statement.

create table in sql

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 create 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

Creating a basic table involves naming the table and defining its columns and each column's data type in data management. Through a Data Management Certification, you can gain essential skillsets. .

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: Top 50 SAS Interview Questions Guide For Fresher, Experienced

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:

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. Learn more through Janbask Training's comprehensive SQL tutorial for beginners & professionals.

Steps to Create Table in Sql: Example

The HR or senior managers ask you to create a table with EMPLOYEE ID, FIRST AND LAST TIME, GENDER, AND EMAIL ID. How to create a table in SQL in this case? Follow the script below.  

Input Commands
CREATE TABLE Employee(
E_ID INT PRIMARY KEY,
E_Name VARCHAR(25),
E_Gender VARCHAR(1),
E_Email_Id NVARCHAR(20) UNIQUE;

INSERT INTO Employee VALUES (1, 'Samantha', 'M', 'sa.com');
INSERT INTO Employee VALUES (2, 'David', 'M', 'da.com');
INSERT INTO Employee VALUES (3, 'Rachel', 'F', 'ra.com');
INSERT INTO Employee VALUES (4, 'Alice', 'F', 'al.com');

SELECT * FROM Employee;

Output

Table 1

 

So, how does this script work to create a new table and to create a new column SQL?

The script will create - 

  • 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 values 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.

Besides steps to add column in SQL, when you create database objects, it is a wonderful practice to see whether an object already exists or not. It prevents error and helps in managing redundancy or duplicate entries that ultimately helps in improving the overall performance of a database script. 

Please Note: 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:

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. 

Also, Note: 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.

Now, that you know how to create a table and a table script in SQL Server Management Studio, it's time to learn how to add columns in SQL. 

Are you looking for a job as a DBA or Developer? Or are you preparing for an interview or test? Check out the Top 50 DB2 Interview Questions and Answers and crack your next interview with confidence!

SQL ALTER TABLE Statement

In SQL, create new column using the ALTER TABLE statement. This program helps to add column in SQL. You can also 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 Columns in SQL?

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

ALTER TABLE table_name ADD column_name column_definition;

Now, as per the above example of creating an employee data table, your manager suddenly asks you to add ‘ADDRESS’, how to do it? 

Check here! 

Input Values

ALTER TABLE Employee
ADD E_Address NVARCHAR(30),
SELECT * FROM Employee;

Output

table 2

 

Develop strong practical skills to become a self-assured pro-SQL! Join the JanBask Training to learn more. 

How to Add Multiple New Columns to the Table?

With the ALTER TABLE statement, it is not necessary to add column in SQL 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 - ‘CONTACT NUMBER’ and 'SALARY' to the employee table. In this case, the above basic syntax can be written as:

ALTER TABLE Employee ADD Contact_Number VARCHAR(50),
Salary VARCHAR(40);

With this syntax, you can add two columns successfully where the limit is set to 50 characters for the ‘CONTACT NUMBER’ column and 40 characters for the ‘SALARY’ column.

The Output will look like -

table 3

You may not find any difference in the output as per the above image. So, just add an integer number in the first row of the table as per the above image with the UPDATE statement, and check if it accepts the data. If your column gets modified, it will definitely accept the data. You can then insert it into the row.

UPDATE Employee
SET E_Salary = 50000
WHERE E_ID = 1;
SELECT * FROM Employee;

Output

Now, you will find the updated data modified in the above column as INT. Get updated with each and every update on JanBask SQL Community.

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.

Take a peek at our guide on SSRS Sub Reports  to find out how to deploy an SSRS report file into the production server and manage it using the report manager tool

How to modify a column in an existing table?

In SQL create new column and modify it with ALTER 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;

If we take the above example where we need to modify the column ‘SALARY’. Salaries are either in INT or Decimal. Hence, the script in such a case will be -

ALTER TABLE Employee
ALTER COLUMN E_Salary INT;
SELECT * FROM Employee;

The Output will be -

NOTE: Feel free to share your experience of being a SQL developer with us, via the Comment section! And, don’t forget to play our SQL Quiz and enhance your knowledge accordingly.

How to Drop a Column in an Existing Table?

Besides steps to add column to SQL table, you can drop columns too! 

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

ALTER TABLE table_name DROP COLUMN column_name;

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

ALTER TABLE Employee DROP COLUMN Address;

With the syntax, the ‘ADDRESS’ column will be dropped from the table Employee, and you cannot use it anymore.

Writing a complex SQL query can be difficult as it involves joins across different tables and multiple nested conditions. Check out our guide on how to write a complex SQL query and break it down into simple steps.

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

SQL Server Training & Certification

  • No cost for a Demo Class
  • Industry Expert as your Trainer
  • Available as per your schedule
  • Customer Support Available

How to Rename a column in an existing table?

Besides knowing how to create column SQL, you are now aware of how to rename, modify and drop columns. 

Here, we have a bonus for you! 

How to rename a Table in SQL server? Yes, to rename a table in SQL Server, you cannot use the ALTER TABLE statement, but you can use the 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 'Employee', 'Employees’';

Output

table 6

Booyah! 

In the above image, the ‘Employee’ table name is changed to ‘Employees’ successfully.

In between, are you planning for certification in SQL server? 

Check Out: How To Become SQL Certified:Boost Your Career & Income With Right Certification

How to Rename an Existing Table?

Besides knowing how to create column SQL, you are now aware of how to rename, modify and drop columns.

Here, we have a bonus for you! 

How to rename a Table in SQL server? Yes, to rename a table in SQL Server, you cannot use the ALTER TABLE statement, but you can use the 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 'Employee', 'Employees’';

Output

table 5

Booyah! 

In the above image, the ‘Employee’ table name is changed to ‘Employees’ successfully.

In between, are you planning for certification in SQL server? 

What are the Limitations and Restrictions?

In SQL create a new column with the ALTER TABLE statement. The columns are automatically added to the end of a table. To add columns in SQL 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 databases. It is always pleasing to our eyes to add 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.

Input

ALTER TABLE Employees
DROP COLUMN E_LName;

Output

  • Now, you must add the ‘E_LName’ with the help of GUI and place it by the side of the ‘E_Name’ column.
  • 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.

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

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

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 this with the ALTER Table statement again. Let us see how to do it.

Also, if you want to gain complete insight about an SQL Developer. Check out how to become an SQL developer with a click.

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 an 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 an 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 an 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 an 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 an 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 an ALTER TABLE statement in SQL.

ALTER TABLE table_name ALTER COLUMN column_name new_data_type;

A data warehouse is a centralized storage facility, which means that all of your data can be kept in one place. This makes it incredibly easy to retrieve and sort through your data, as well as share it with other people. To know more read our guide on Data Warehouse.

Final Thoughts

You can easily add column in SQL using the ALTER TABLE. Our tricks mentioned above are a complete guide on the same along with real-life examples. Now you must note down these steps somewhere and try implementing them on your own. This will be a holistic learning opportunity for you. 

However, ALTER TABLE command will be a Child’s Play if you have some background related to it or if you sharpen up your skills with the right certification. Then all these tricks will be easy to practice in real-life cases. Whether you have those credentials or not, our affordable online SQL server training at JanBask Training along with free career consultation will help you learn from scratch to develop commands on database systems. Join our comprehensive SQL Server Certification to be industry-ready!

Why wait? We are just a call away! 

For the time being, do share your experience after implementing our tricks in the comment section. 

Also, subscribe to our newsletter to stay tuned to such insightful information! 

All the Best!

fbicons FaceBook twitterTwitter google+Google+ lingedinLinkedIn pinterest Pinterest emailEmail

     Logo

    Jyotika Prasad

    Through market research and a deep understanding of products and services, Jyotika has been translating complex product information into simple, polished, and engaging content for Janbask Training.


Comments

  • J

    Jax Williams

    Hi! This blog is very informative. Can we have some more information about careers in SQL database?

     Reply
    • Jyotika  User

      JanbaskTraining

      Hey, thanks man for your interest! For further information, you can contact us at https://www.janbasktraining.com/contact-us

  • A

    Amari Jones

    I am planning to go for a SQL server certification, can I get some more information on the same?

     Reply
    • Jyotika  User

      JanbaskTraining

      Hey, thanks a ton for your comment. We feel that you connect with our experts at https://www.janbasktraining.com/contact-us

  • Z

    Zane Brown

    This information about the add column in SQL is enlightening. I was not able to achieve this earlier. But after reading this guide, I implemented, and got success! Thanks a ton JanBask!

     Reply
    • Jyotika  User

      JanbaskTraining

      Hey thank you so much. We are grateful that our blog has been a help to you! Do connect us for any further enquiry at https://www.janbasktraining.com/contact-us

  • E

    Emilio Davis

    Hey, the last part of the blog is quite beneficial as I often wondered how to rename a table in SQL. Thanks JanBask Training!

     Reply
    • Jyotika  User

      JanbaskTraining

      Hey, this has made our day! Thanks a ton that you liked our blog! We request you to connect with us to know more such enlightening information about qa testing at https://www.janbasktraining.com/contact-us

  • K

    Knox Miller

    Hi, the blog is very insightful. However, I am also looking for some salary related information in SQL.

     Reply
    • Jyotika  User

      JanbaskTraining

      Thank you for showing interest in us. We will request you to reach out to our education experts at https://www.janbasktraining.com/contact-us

Trending Courses

AWS Course

AWS

  • AWS & Fundamentals of Linux
  • Amazon Simple Storage Service
  • Elastic Compute Cloud
  • Databases Overview & Amazon Route 53
AWS Course

Upcoming Class

4 days 09 Dec 2022

DevOps Course

DevOps

  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing
DevOps Course

Upcoming Class

3 days 08 Dec 2022

Data Science Course

Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning
Data Science Course

Upcoming Class

4 days 09 Dec 2022

Hadoop Course

Hadoop

  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation
Hadoop Course

Upcoming Class

4 days 09 Dec 2022

Salesforce Course

Salesforce

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL
Salesforce Course

Upcoming Class

4 days 09 Dec 2022

QA Course

QA

  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing
QA Course

Upcoming Class

4 days 09 Dec 2022

Business Analyst  Course

Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum
Business Analyst  Course

Upcoming Class

4 days 09 Dec 2022

MS SQL Server Course

MS SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design
MS SQL Server Course

Upcoming Class

4 days 09 Dec 2022

Python Course

Python

  • Features of Python
  • Python Editors and IDEs
  • Data types and Variables
  • Python File Operation
Python Course

Upcoming Class

12 days 17 Dec 2022

Artificial Intelligence  Course

Artificial Intelligence

  • Components of AI
  • Categories of Machine Learning
  • Recurrent Neural Networks
  • Recurrent Neural Networks
Artificial Intelligence  Course

Upcoming Class

4 days 09 Dec 2022

Machine Learning Course

Machine Learning

  • Introduction to Machine Learning & Python
  • Machine Learning: Supervised Learning
  • Machine Learning: Unsupervised Learning
Machine Learning Course

Upcoming Class

39 days 13 Jan 2023

Tableau Course

Tableau

  • Introduction to Tableau Desktop
  • Data Transformation Methods
  • Configuring tableau server
  • Integration with R & Hadoop
Tableau Course

Upcoming Class

4 days 09 Dec 2022

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews