SQL Server - DEFAULT

The SQL Server (Transact-SQL) DEFAULT constraint is used to set the default value for a column. The column takes default value when a new record is inserted without specifying any value.

SQL Server DEFAULT constraint with CREATE TABLE

The below mentioned statement creates a table called Employee which contains five columns: EmpID, Name, City, Age and Salary in which DEFAULT constraint is applied on column City.

CREATE TABLE Employee (
  EmpID INT NOT NULL,
  Name VARCHAR(255),
  City VARCHAR(100) DEFAULT 'London',
  Age INT,
  Salary DECIMAL(18,2)
);

The DEFAULT constraint can also be used to insert system values, by using functions like CURRENT_TIMESTAMP:

CREATE TABLE Orders (
  OrderID INT NOT NULL,
  OrderQuantity INT NOT NULL,
  OrderPrice DECIMAL(18,2),
  OrderDate DATETIME DEFAULT CURRENT_TIMESTAMP
);

SQL Server DEFAULT constraint with ALTER TABLE

In the above example, a table called Employee is created. To enforce DEFAULT constraint on City column, the statement is given below:

/* Enforces a DEFAULT constraint named DV_City */
ALTER TABLE Employee
ADD CONSTRAINT DV_City
DEFAULT 'London' FOR City;

DROP DEFAULT constraint

To drop DEFAULT constraint from table called Employee, the statement is given below:

/* Drops a DEFAULT constraint named DV_City */
ALTER TABLE Employee
DROP CONSTRAINT DV_City;