Add a column with a default value to an existing table in SQL Server 
Published on Jan 29, 2020
For any given project that has been existence for any amount of time, at some point your table design will change and you will need to add a new column to your SQL table. More specifically, this value should be NOT NULL so you want/need to set a default value when adding the new column.Adding default constraint with SQL
To add a new column, you need to perform an ALTER TABLE ADD COLUMN to create the new column:
ALTER TABLE {TABLENAME}
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL}
This will create a new column with the name you give it. To add a default constraint, you need to extend the command to include the following:
ALTER TABLE {TABLENAME}
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL}
DEFAULT {default_value}
Now my DBA doesn't like this because it creates a unique name with some gobbly gook at the end, e.g. DF__MyTable__MyColumn__6FC7AEG8. This of course is random each time you run it, which makes it more difficult to delete it because it won't be consistent on different databases that you may run this script.
To add with column with a default constraint and a specific name you can run:
ALTER TABLE {TABLENAME}
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL}
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
