How to Add a Default Value to a Column in SQL Server

In SQL Server, adding a default value to a column ensures that a specified value is inserted into the column whenever a new row is added and no value is provided for that column. This is particularly useful when you want to ensure a column has a specific value when no explicit value is provided, such as setting a default value of `0` for numeric columns or `CURRENT_TIMESTAMP` for datetime columns.

Syntax for Adding a Default Value

The basic syntax to add a default value to an existing column in SQL Server is:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name
DEFAULT default_value FOR column_name;
                                

Example: Adding a Default Value

Consider the following example, where we have a table called Employees and we want to add a default value of `1000` to the Salary column:

ALTER TABLE Employees
ADD CONSTRAINT DF_Employees_Salary
DEFAULT 1000 FOR Salary;
                                

This statement adds a default value of `1000` to the Salary column. The constraint is named DF_Employees_Salary, which can be used to drop the constraint later if needed.

Example: Setting a Default Value for a Date Column

If you want to set the default value for a datetime column to the current date and time, you can use the CURRENT_TIMESTAMP function. Here's an example:

ALTER TABLE Employees
ADD CONSTRAINT DF_Employees_HireDate
DEFAULT CURRENT_TIMESTAMP FOR HireDate;
                                

Modifying the Default Value of an Existing Column

If a default constraint already exists on a column and you want to modify the default value, you need to first drop the existing constraint and then add a new one. Here is an example:

-- Drop the existing constraint
ALTER TABLE Employees
DROP CONSTRAINT DF_Employees_Salary;

-- Add a new default value
ALTER TABLE Employees
ADD CONSTRAINT DF_Employees_Salary
DEFAULT 2000 FOR Salary;
                                

Removing the Default Value

If you no longer want a default value for a column, you can remove the default constraint with the following command:

ALTER TABLE Employees
DROP CONSTRAINT DF_Employees_Salary;
                                

Note that this will remove the default value for the Salary column, and new rows will need to explicitly provide a value for that column.

Conclusion

Adding a default value to a column in SQL Server helps ensure that new rows have a consistent value, even if no value is provided for a specific column. It's a simple yet powerful feature to maintain data integrity and reduce errors in your database.