How to Alter column from Not Null to Null in SQL Server Table - SQL Server / T-SQL Tutorial Part 54


You are working as SQL Server developer for Auto Insurance Company, You were asked to create a table with below definition where Middle Name has to be provided always. So you have created the Column with Not Null property. After some time, your company encountered some data ,where customer had no Middle Name. They would like you to change the Column to accept Null if not provided. What scripts you will provide?

Create Table Customer
(FirstName VARCHAR(50) Not Null,
MiddleName VARCHAR(50) Not Null,
LastName VARCHAR(50),
Age SmallInt,
PhoneNumber CHAR(9),
DOB Date,
Gender CHAR(1)


You can simply Alter the column by using below statement.

Alter table dbo.TableName
Alter Column ColumnName DataType Size Null

To Alter Middle Name to accept Null, we can use below statement.

Alter table dbo.Customer
Alter Column MiddleName VARCHAR(50) Null