To add a column with a default value to an existing table in SQL Server, you can use the ALTER TABLE statement combined with the ADD clause. The general syntax for adding a new column with a default value is as follows:

ALTER TABLE TableName
ADD ColumnName DataType DEFAULT DefaultValue;

Here’s a step-by-step breakdown of the syntax:

1. ALTER TABLE TableName: This specifies that you’re modifying the structure of an existing table named TableName.
2. ADD ColumnName DataType: This part adds a new column named ColumnName with the data type specified by DataType. Replace DataType with the appropriate SQL Server data type for your new column, such as INT, VARCHAR, DATETIME, etc.
3. DEFAULT DefaultValue: The DEFAULT keyword is followed by the DefaultValue that you want to set for the new column. This value will be used for the new column in all existing rows in the table upon its addition.

Example

Suppose you have a table named Employees and you want to add a new column named IsActive with a data type of BIT (which can hold values 0 or 1), and you want to set the default value to 1 (indicating active). The SQL statement would look like this:

ALTER TABLE Employees
ADD IsActive BIT DEFAULT 1;

This command will add the IsActive column to the Employees table, and all existing rows in the table will have the IsActive column set to 1 by default. For new rows inserted into the table without a specific value provided for IsActive, SQL Server will also automatically assign the default value of 1 to this column.