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
The samples used this articles are in this GitHub Repo. Please feel free to refer and use.
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.