How to Insert a Not Null Column to an Existing Table in SQL Server

You have a table which has huge number of records but due to some requirement you want to Add another Not Null Column to this Table. Normally it is straight forward SQL statement which will do this job-

ALTER TABLE TEST_TABLE ADD NEW_COL INT NOT NULL;

But the above will work if the table has no data. If table has data and above statement is executed then following error message is shown-

To overcome this you can execute following statement to add a Not Null Column-

ALTER TABLE TEST_TABLE ADD NEW_COL INT NOT NULL DEFAULT 20;

Now if there are millions of record in the table then this statement will take ages to execute. Depending upon the DB server resource it may peak CPU/Memory usage. I had to update a table with Not Null column with over 5 million records. Following approach helped me to Add a Not Null Column to the table.

1. First add the column as Null column-

ALTER TABLE TEST_TABLE ADD NEW_COL INT  NULL;

2. Find number of records in table-

Select COUNT(*) from TEST_TABLE;

3. Update default value for all the records in controlled way-

UPDATE TEST_TABLE SET NEW_COL = 25 where PRIMARY_KEY between 1 and 100000

The above statement is repeated until all the records are updated with default value.

4. Confirm that all the records are updated with default value. If any record is not updated then the statement to change the column to Not Null will fail.

Select * from TEST_TABLE where NEW_COL is NULL;

5. Once confirmed that records are updated, execute the statement to convert Null Column to NOT Null column-

ALTER TABLE TEST_TABLE ALTER COLUMN NEW_COL INT NOT NULL;