SQL Server — Add a column with a default value to an existing table

If you are working as a SQL Server developer then you will be responsible for the implementation, configuration, maintenance, and performance of critical SQL Server RDBMS systems and most of the times, you have to follow the agile methodology also.

One of the toughest job is adding columns your existing data table inside SQL Server database with some default values without any failures. There are some points which you should keep in mind the following points, in case you are adding a column in the existing table-

1. If the column is added nullable, then null will be the value used for existing rows
2. Nullable column means that you can insert Null for the columns value
3. If it’s not a nullable column, you have to insert some value of that defined data type
4. Beware when the column you are adding has a NOT NULL constraint, yet does not have a DEFAULT constraint (value)

SQL Syntax for adding a new column in the existing Table — This is very simple SQL Syntax for adding new columns in the existing table in a SQL Server database —

ALTER TABLE {TABLENAME} 
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} 
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
WITH VALUES

Note —

1. Constraint Name is optional for your SQL statement and If you leave out CONSTRAINT namethen SQL Server will autogenerate a Default-Constraint