T-SQL - Add a "NOT NULL" new column to table which has rows of data

When you want to add a new column to an existing table that has rows, in each row it expect a value for the new column, and the default value is NULL. If you try to add a new column which is NOT NULL, you will encounter with error.

The easiest solution is to alter the table and define the column to allow NULLs, then add in the default data values using the UPDATE T-SQL command, and next, alter the column to NOT NULL.

ALTER TABLE AdventureWorksLT.SalesLT.Customer
ADD temp int NULL

GO

UPDATE AdventureWorksLT.SalesLT.Customer
SET temp = 0

GO

ALTER TABLE AdventureWorksLT.SalesLT.Customer
ALTER COLUMN temp int NOT NULL

GO

T-SQL - Add a "NOT NULL" new column to table which has rows of data