I created a new Column S_TEST and set its default value to 2 and save the table, but unfortunately the old records were not set automatically, instead they have NULL values. See my screenshots.
2 Answers
If you add a new column which is not null then the default constraint will automatically populate the column with the default value for existing rows.
If you add a new column which is nullable the DDL statement should have WITH VALUES option to force setting of the default values for existing rows.
ALTER TABLE dbo.tab ADD Col int NULL DEFAULT 10 WITH VALUES ;
Think this way, DBs add new columns with NULL values, even it has a default assigned. To avoid that, make it not NULL:
This works for me on SQL 2016:
ALTER TABLE [DB].[MY_TABLE] ADD MyText VARCHAR(255) NOT NULL DEFAULT 'yes'
Another way of reaching the same, is creating a VIEW on the top of our table.
Related