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?

the definition of `default` means, it can't be `null` and there is value `10` always — techspider, Sep 27 '16 at 20:56
try adding `WITH VALUES` at the to add `DEFAULT` for `non-nullable` columns; if you want to update existing rows — techspider, Sep 27 '16 at 20:57

dfundako · Accepted Answer · 2016-09-27T21:02:26.673

6

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

edited Sep 27 '16 at 21:02

answered Sep 27 '16 at 20:58

dfundako

8,022
3
18
34

Second query it is not setting as by default it is nullable column – Kannan Kandasamy Sep 27 '16 at 21:01
Was just showing both options so you can see the difference. Removing to reduce confusion and leaving just the answer. – dfundako Sep 27 '16 at 21:03

score 0 · Answer 2 · answered Sep 27 '16 at 21:18

The Default column value is applied to new rows inserted into the table after the default column value is defined. To test this, try an INSERT command like this.

INSERT INTO TableName col1 VALUES (col1_value)

After this INSERT, the col2 value should get the Default value of 10 for the inserted row.

However, existing rows in the table do not automatically get assigned the new Default column value

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

2 Answers2