[T-SQL] Add new column [Not Null] column in a table already having data - Part 2

Previously i wrote an article with a method that how can we include not null column in a table already having data.

But that method has number of scripts to execute. SQL Server handle it in a very good way.

If you execute the script

ALTER TABLE TestTable
ADD ThirdCol INT NOT NULL DEFAULT(0)
GO

Then SQL Server will itself insert the default value 0 for the rows which are already present in database.

[T-SQL] Add new column [Not Null] column in a table already having data

This is a very simple question and asked many times, and answer is much simpler then the question.

Here is one example that we have to add a Transactiondate column into table CommissionEarned.

So here are the steps:

1. Add column without specifying not null
ALTER TABLE COMMISSIONEARNED ADD [CALCULATEDDATE] [datetime]
2. Add default constraint if its your business requirement
ALTER TABLE COMMISSIONEARNED ADD CONSTRAINT [DF_CE_CALCULATEDDATE] DEFAULT (getdate()) FOR [CALCULATEDDATE]
3. Update field with the data according to your business logic
UPDATE COMMISSIONEARNED SET CALCULATEDDATE=GETDATE()
4. Update field so that it should not entertain not null any more
ALTER TABLE COMMISSIONEARNED ALTER COLUMN [CALCULATEDDATE] [datetime] NOT NULL

Let me know for any questions