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,

Fig.1 Columns with NULL records

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Website Built with WordPress.com.

Up ↑

%d bloggers like this: