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.

SQL SERVER - Altering Column - From NULL to NOT NULL AlteringColumn-800x260

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 = ''

Step 2: Change Column NULL Values


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)

SQL Column, SQL Scripts, SQL Server
Previous Post
SQL SERVER – Quick Look at Suspected Pages
Next Post
SQL SERVER – Applying Query Hints to Views

Related Posts

1 Comment. Leave new

  • Scott Hungarter
    February 24, 2024 7:50 pm

    Hello good sir… I am trying this and despite verifying that there are currently NO ROWS that currently have a NULL value in the column, and also dropping any indexes referencing the column I am trying to alter, I get the following very scary error:

    Msg 596, Level 21, State 1, Line 6
    Cannot continue the execution because the session is in the kill state.
    Msg 0, Level 20, State 0, Line 6
    A severe error occurred on the current command. The results, if any, should be discarded.

    Any suggestions would be greatly appreciated!


Leave a Reply