SQL Server Alter Table with Add Column

There are many scenarios where we need to do structural changes to a table, i.e. adding a column, modifying data type, dropping a column and so on. This change to an existing table is possible by issuing a SQL Server Data Definition Language (DDL) T-SQL command known as Alter Table.

In this article we are specifically focusing on adding a new column in a table using SQL Server Alter Table.

Scenario 1: Adding columns to empty table

When the table is created and if there are no records in that table, then there is no problem in adding a column or multiple columns to that table.

Let us see the following example:

create table customers1
(
  custid varchar(5),
 custname varchar(50)
)
go

Scenario 1.a: Adding single column:

After creating the table, we realize that date of birth (DOB) column needs to be added. The following command will help us in achieving this.

Alter Table customers1
Add DOB Date
go

Scenario 1.b: Adding multiple columns:

Adding two more columns is also very easy. Let us add nationality and gender as two more columns by just having a comma separator in each definition.

Alter Table customers1
Add Nationality Varchar(40), Gender Char(1)
go

Scenario 1.c: Adding columns with constraints:

Adding a column with constraint such as primary key, unique key, foreign key, not null, check or default is again not challenging in an empty table.

Note – A primary key constraint cannot be added on the new column if there is already a primary key on any other existing column.

Let us add SSN and deposit amount as two columns, SSN having primary key and deposit amount having check constraint.

Alter Table customers1
Add SSN Varchar(10) Primary Key, 
Deposit_Amount Integer Check(Deposit_Amount >= 200)
go

Scenario 2: Adding columns to table having records

When a column is added to a table with existing records, then for those records, by default a null value is assigned by SQL Server.

Adding column on table having existing records.

Let us create table customers2 and add 2 records in it.

create table customers2
(custid varchar(5),
 custname varchar(50)
 )
 Go
Insert into customers2 Values('C1','John')
Insert into customers2 Values('C2','Martin')
go

Scenario 2.a: Adding columns with default null values on existing records.

Alter Table customers2
Add DOB Date
go

Let us see the table Customers2

select * from customers2

Image2-No-Default-Value

Scenario 2.b: Adding columns with default value

It might happen that you want to add a column specifying some default value and there are existing records in the table. But in the default setup, those existing records will not get that value.
Let us add token amount as a column to this Customers2 table with default value as 100.

Alter Table customers2
Add Token_Amount Integer Default 100
go

Let us see the table Customers2

select * from customers2

Image2-No-Default-Value

Note -The default value will be effective when new record is added.

Scenario 2.c: Adding columns with default value and applying those values on existing records.

Let us add another column tax with default value as 50. This value needs to be added to the existing records as well. We need to explicitly specify the clause With Values.

Alter Table customers2
Add Tax Integer Default 50 With Values
go

Image3-Default-Value-WithClause

Scenario 2.d: Adding columns with Not Null constraint on existing records.

Let us add a column Country with Not Null constraint. Now this will be contradictory because the earlier two records cannot have null value for Country.

Let us try.

Alter Table customers2
Add Country Varchar(50) Not Null
go

It will throw the following error.

ALTER TABLE only allows columns to be added that can contain nulls

To rectify this, we need to mention the default value which will safeguard the earlier records.

Scenario 2.e: Adding columns with not null constraint and default value on existing records

Let’s have India as the default value.

Alter Table customers2
Add Country Varchar(50) Default 'India' Not Null
go

Now this works fine. Let us see the table again.

select * from customers2

Image4-Default-Value-NotNull

Conclusion:

In this article, we have seen the following scenarios of using ALTER table to add column to a SQL Server table:

1. Adding one or multiple columns to an empty table.
2. Adding column to table having records. Those records get default value as null.
3. Adding column with default value to table having records

• In absence of With Values clause
• In presence of With Values clause

4. Adding column with not null constraint to table having records

• In absence of Default clause
• In presence of Default clause