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.

• Read Only Tables – Is it Possible? – SQL in Sixty Seconds #179
• One Scan for 3 Count Sum – SQL in Sixty Seconds #178
• SUM(1) vs COUNT(1) Performance Battle – SQL in Sixty Seconds #177
• COUNT(*) and COUNT(1): Performance Battle – SQL in Sixty Seconds #176
• COUNT(*) and Index – SQL in Sixty Seconds #175
• Index Scans – Good or Bad? – SQL in Sixty Seconds #174
• Optimize for Ad Hoc Workloads – SQL in Sixty Seconds #173
• Avoid Join Hints – SQL in Sixty Seconds #172
• One Query Many Plans – SQL in Sixty Seconds #171

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