SQL Server – Add Column Default Value

Send Us a Sign! (Contact Us!)
--> (Word) --> (PDF) --> (Epub) --> (Text)
--> (XML) --> (OpenOffice) --> (XPS)

In SQL Server there are two ways to add a column with a default value.

Add Default Value to Existing Column
  • Add default to existing column DateOfHire:
  • ALTER TABLE [dbo].[Employees] ADD  DEFAULT (getdate()) FOR [DateOfHire]
  • Add default value to existing column IsTerminated
  • ALTER TABLE [dbo].[Employees] ADD  DEFAULT ((0)) FOR [IsTerminated]
    Add New Column with Default Value
  • Add new column DateOfHire with default
  • ALTER TABLE Employees ADD DateOfHire datetime DEFAULT (GETDATE())
  • Add new column IsTerminated with default
  • ALTER TABLE Employees ADD IsTerminated datetime DEFAULT (0)

    Add Default Value with Create Table

    CREATE TABLE [dbo].[Employees]
    (
    [EmployeeID] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [varchar](50) NULL,
    [LastName] [varchar](50) NULL,
    [SSN] [varchar](9) NULL,
  • Add default of zero
  • [IsTerminated] [bit] NOT NULL DEFAULT ((0)) ,
  • Add default of getdate()
  • [DateAdded] [datetime] NULL DEFAULT (getdate()),
    [Comments] [varchar](255) NULL,
    [DateOfHire] [datetime] NULL
    )