Alter Table 'DEFAULT' not setting default values - SQL Server

Question

I am trying to alter an existing column and adding an integer column. While adding integer column i am setting DEFAULT as 10 but default is not setting while adding the column

ALTER TABLE dbo.Contacts ADD Col1 INT DEFAULT 10

I require to do explicit update script to set all past records as default values

Whereas if the column is 'not null' then it is defaulting to 10 for all the records.

ALTER TABLE dbo.Contacts ADD Col2 INT NOT NULL DEFAULT 10

Why nullable column is not setting with default value? In my actual table I have close to 850 mil records and my new column is nullable column and I am trying to avoid one more update after my ALTER table script. What is the way?

I am thinking of creating not nullable column with default as 10 and alter again the constraint to NULL. Is this fine approach? Any other ways?

Answer 1

You have to use WITH VALUES to get the value and not the null. Using WideWorldImporters on 2016:

ALTER TABLE sales.orders 
ADD c1 INT DEFAULT 10 WITH VALUES
GO