Adding DEFAULT value for a column in SQL Server

By Ashish Upadhyay


What is Default Value in SQL Server? 

Default is a constraint in the SQL Server which provides the ability can be applied to a column to give it a default value when a row is created and no value is supplied in the INSERT statement. The default value can be a constant value either a number, string or a value out of function, for example GETDATE(), NEWID() etc.

A column can have only one DEFAULT definition. Default values can be assigned to nullable and required columns. In the case of a nullable column, the default value will be used when an insert occurs and the column name is excluded from the statement. To insert a null value, the column must be included in the list and the value of null must be inserted explicitly.
Default constraints can also be used if you need to add a new column in your existing table and the table already has data. In this case you can use default to apply a specific value across all the existing records in the table.


Script to Add Default Value with CREATE TABLE Statements

CREATE TABLE Employees        

       ( 
                 EmployeeID INT IDENTITY(1,1) NOT NULL,

                 FirstName VARCHAR(30) NOT NULL,
                 MiddleName VARCHAR(30) NULL,
                 LastName VARCHAR(30) NULL,
                 PhoneNo VARCHAR(20) NULL,
                 DateOfJoining DATE NOT NULL DEFAULT (GETDATE()),
                 RecordCreatedOn DATETIME NULL DEFAULT (GETUTCDATE())

         )

Script to Add Default Value while adding new Columns

CREATE TABLE Employees

          (

              EmployeeID INT IDENTITY(1,1) NOT NULL,

              FirstName VARCHAR(30) NOT NULL,

              MiddleName VARCHAR(30) NULL,

              LastName VARCHAR(30) NULL,

              PhoneNo VARCHAR(20) NULL

            )

ALTER TABLE Employees ADD DateOfJoining DATE DEFAULT (GETDATE())
ALTER TABLE Employees ADD RecordCreatedOn DATETIME DEFAULT (GETUTCDATE())

Script to Add Default Value on Existing Columns
CREATE TABLE Employees
(
        EmployeeID INT IDENTITY(1,1) NOT NULL,
        FirstName VARCHAR(30) NOT NULL,
        MiddleName VARCHAR(30) NULL,
        LastName VARCHAR(30) NULL,
        PhoneNo VARCHAR(20) NULL,
        DateOfJoining DATE NOT NULL DEFAULT (GETDATE()),
        RecordCreatedOn DATETIME NULL DEFAULT (GETUTCDATE())

  )

ALTER TABLE Employees ADD DEFAULT (GETDATE()) FOR DateOfJoining

ALTER TABLE Employees ADD DEFAULT (GETUTCDATE()) FOR RecordCreatedOn

Adding a Default Value Using SQL Server Management Studio (SSMS)
The Default value can also be added using the table designer in SQL Server Management Studio (SSMS). While creating a new table or modifying the existing table using table designer you need to specify the default value in the "Column Properties" section in the property called "Default Value or Binding". Make sure the value you provided should be match with the Datatype you have specified.