We can add a column to the table in two ways. First is using sql server management studio graphical interface. Second one is using SQL command ADD COLUMN.


ADD <Column> <Data Type> NULL (or) NOT NULL

Column will be created on the end of the table. If the table into which we want to add new column is not empty (with 1 or more rows), we need to create column with NULL values property. The operation will in opposite case result in an error.

Example of Column Addition Using ADD COLUMN Statement

1) We create table with employees dbo.Employee containing 3 fields

  • ID – column with primary key starting from 1 and ascending with every added record by 1: IDENTITY(1,1). More follows.
  • name of employee
  • surname of employee

2) We add 2 records into the table – employee Daenerys Targaryen and Yennefer from Vengerberg

3) We add column “Salary”


1) Create table

CREATE TABLE dbo.Employee (
   [Name] VARCHAR(255) NOT NULL,
   [Surname] VARCHAR(255) NOT NULL

2) Inserting data into the table

INSERT INTO dbo.Employee ([Name], [Surname])
VALUES ('Daenerys','Targaryen'),
('Yennefer', 'Vengerberg');

ADD COLUMN Example - inserting data into the table

3) Inserting new column “Salary”

ALTER TABLE dbo.Employee
ADD [Salary] INT NULL;

After adding of the new column are values of column “Salary” NULL. It is, therefore, suitable to initiate table UPDATE

Note: If we try to add a column with NOT NULL property, the result will be an error

ALTER TABLE dbo.Employee

ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column ‘Salary’ cannot be added to non-empty table ‘Employee’ because it does not satisfy these conditions.

5/5 - (1 vote)

Jan Zedníček - Data & Finance

My name is Jan Zedníček and I work as a freelancer. I have been working as a freelancer for many companies for more than 10 years.
I used to work as a financial controller, analyst and manager at many different companies in field of banking and manufacturing. When I am not at work, I like playing volleyball, chess, doing a workout in the gym and I enjoy tasting of best quality rums.
I am trying to summarize all my knowledge on this website not to forget them and to put them forward to anyone.

Leave a Reply

Your email address will not be published. Required fields are marked *