SQL SERVER – Altering Column – From NULL to NOT NULL

Let us see a very simple script today. It is about Altering Column – From NULL to NOT NULL. I recently had to perform this task for my client during the Comprehensive Database Performance Health Check.

Well, the process of updating the column from null to not null is very straight forward but lots of people often get an error and are not able to move forward because they try to make convert the column which only contains null and when they try to convert that to NOT NULL, they face error due to that NULL value.

Here is two steps process to convert NULL to NOT NULL. We will assume that we have a column with a data type of VARCHAR and we are trying to convert it to allow NOT NULL Values.

Step 1: Convert all values to NOT NULL

UPDATE TableName
SET ColumnName = ''
WHERE ColumnName IS NULL

Step 2: Change Column NULL Values

ALTER TABLE TableName 
ALTER COLUMN ColumnName VARCHAR(100) NOT NULL

Well, that’s it. We are done with Altering Column.

If you liked this blog, please do not forget to subscribe to my YouTube Channel – SQL in Sixty Seconds.

Here are my few recent videos and I would like to know what is your feedback about them.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

Menu
Exit mobile version