Syntax:
ALTER TABLE {Table_Name}
ADD {Column_Name} {NOT NULL|NULL}
CONSTRAINT {Constraint_Name} DEFAULT {Default_Value}
WITH Values
Have a look at this video on ALTER TABLE STATEMENT in SQL
Let’s understand this via an example:
ALTER TABLE TableA
ADD Column1 Bit NULL --Or you can also use NOT NULL
CONSTRAINT D_TableA_Column1 --when the Constraint is deleted a default constraint name will be generated automatically
DEFAULT (0) --Default constraint, not mandatory to use.
WITH values --If you want to add a default value for existing records and column is nullable then add this.
Note:
When you use optional With-Values Statement:
The WITH VALUES is only needed if you want to add a default value for existing records and column is nullable then add this. It will automatically use the default Value for all existing records, whether you specify WITH VALUES or not if the Column is NOT NULL.
You can refer to this video to understand how CONSTRAINTS works.
When you use Optional Constraint Name:
If you’ll not use CONSTRAINT D_TableA_Column1 then SQL Server will generate a Default-Constraint automatically with a different Name like: DF__TableATa__Column1C__4FB7FEF6
Let’s understand how Inserts work with a Default-Constraint: If you insert a Record into TableA and do not Specify Column1’s value, then it will Default to 0. But, if you insert a Record and Specify SomeCol's value as NULL (and your column allows nulls), then the Default-Constraint will not be used, and NULL will be inserted as the Value.
