How to add non-nullable columns with default values.

Question

Often we need to add a non-nullable column to a table, and it is quite a mission. Using a default constraint as is doesn’t work, so we have to create nullable columns, update them to default values, then make them non-nullable. Is there not an easier way to do this?

Answer 1

Yes, the WITH VALUES modifier to a DEFAULT constraint applies the default value to existing rows, eliminating 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