How to add nullable columns with default values

Question

Often we need to add a column with invalid values ​​to the table, and this is quite a mission. Using the default constraint does not work as well, so we need to create columns with a null value, update them to the default values, and then invalidate them. Is there an easier way to do this?

Answer 1

Yes, the WITH VALUES modifier to limit DEFAULT applies the default value to existing lines, excluding all the "hard" work described in the question.

IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id=OBJECT_ID('[caConfig]') AND [Name]='ExportWizardVersion')
ALTER TABLE [caConfig] 
    ADD 
        [ExportWizardVersion] varchar(5) not null CONSTRAINT DF_caConfig_ExportWizardVersion DEFAULT '5.8' WITH VALUES,
        [ExportPeriodEnd] varchar(10) not null CONSTRAINT DF_caConfig_ExportPeriodEnd DEFAULT 'MonthEnd' WITH VALUES