Altering Column – From NULL to NOT NULL in SQL Server

Introduction

When working in SQL Server tables, sometimes we may need to change a column that allows NULL to NOT NULL. This change is required as part of business requirement, that is definitely this column contains value or else as part of performance improvements, we may be in need to change the column from NULL to NOT NULL.

From NULL to NOT NULL

Well, the process is very simple, but sometimes we might have ended in an error because those columns already have NULL values. In this article, We will see a simple step to Alter the Nullable column to the Not Nullable column.

To make it simpler, let’s create a table with Nullable columns and insert few records.

Creating a Table

CREATE TABLE Demo.StudentDetails 
(
	StudentId INT PRIMARY KEY IDENTITY(1,1) NOT NULL, 
	FirstName NVARCHAR(25) NOT NULL, 
	MiddleName NVARCHAR(25) NULL, 
	LastName NVARCHAR(25) NULL
)

Insert some Records

INSERT INTO Demo.StudentDetails (FirstName) VALUES ('Sundaram'), ('Sarwan'),('Karthik'),('Pushpa'),('Akilan'),('Pattal')

Now our table results as,

In our example table, MiddleName and LastName columns are Nullable, that is it allows NULL. Now let’s change the LastName column from NULL to NOT NULL.

Step 1: Convert the NULL values to Empty

Since our column’s datatype is NVARCHAR, we can convert NULL to Empty by doing this,

UPDATE Demo.StudentDetails SET 
LastName=''
WHERE LastName IS NULL

Step 2: Alter the Column from NULL to NOT NULL

ALTER TABLE Demo.StudentDetails 
ALTER COLUMN LastName NVARCHAR(25) NOT NULL

GitHub Repo

The samples used this articles are in this GitHub Repo. Please feel free to refer and use.

Conclusion

In this article, we have discussed how simply we can convert a column from NULL to NOT NULL even though the column has NULL values. I hope you all found this article much useful. Please share your feedback in the comment section. We will learn other tips and tricks in our upcoming articles.