Add Column Default Value

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
)

2 comments
Sithembiso Matiwane 26 Sep 2013 at 2:22 pm

i dont know when you do this, you do it the way you create a stored procedure or how? help me

Sunil 05 Dec 2012 at 10:49 am

This is very useful article. Thanks a lot!

Featured Articles

 Site Author