Default constraint in SQL Server allows you to set default value for a column in SQL Server database. Once you add default constraint to a column, then its default value will be automatically added to all new records, unless you specify its value. In this article, we will look at how to add default value in SQL server.
How To Add Default Value in SQL Server
There are two ways to add default value in SQL Server. You can add default constraint in SQL Server when you create new table, or modify an existing table’s column. We will look at both these approaches one by one.
Set Default on Create Table
Here is the syntax to add default value when you create table.
create table table_name( list_of_column_definitions, column_name data_type DEFAULT default_value );
In the above syntax, you need to specify table name and a list of column definitions. When you mention the column whose default value you want to set, also specify DEFAULT keyword followed by the default value you want to add.
Here is an example to set default value of column City in table Employees
CREATE TABLE Employees ( ID int NOT NULL, Last_Name varchar(255) NOT NULL, First_Name varchar(255), Age int, City varchar(255) DEFAULT 'New York' );
As you can see, in the City column’s definition, we also add DEFAULT keyword followed the default value “New York” to be used.
Also read : How to DROP INDEX in SQL Server
Instead of using literal strings, you can also use system functions to specify default value. Here is an example,
CREATE TABLE Sales ( ID int NOT NULL, Order_Number int NOT NULL, Order_Date date DEFAULT GETDATE() );
In the above case, Order_Date column will be populated with system date, unless you specify its value.
Also read : How to DROP VIEW in SQL Server
Set Default on Alter Table
If you already have an existing table, then you need to use ALTER TABLE statement to add default value to a column. Here is the syntax for it:
ALTER TABLE table_name ADD CONSTRAINT constraint_name DEFAULT default_value FOR column_name;
In the above SQL query, you need to specify table name, default constraint name, default value and the column name whose default value you want to set.
Also read : How to Copy data from one table to another
Here is an example to set default constraint in existing column.
ALTER TABLE Employees ADD CONSTRAINT df_City DEFAULT 'New York' FOR City;
Please note, ALTER TABLE will not change the value of existing rows. It will only set default value of new rows added henceforth. So if you want to set default value for existing rows, then you need to use UPDATE statement for it.
As you can see it is very easy to add default value in SQL Server.