Add Column Default Value

You can add a default value to a new OR existing column in SQL Server without dropping it. Specifying a default constraint should not cause any blocking. Only when setting a column to NULL OR NOT NULL will cause blocking. But to set the NULLABLE attribute, you need to use ALTER COLUMN instead.

When adding this default value, it’s always best to specify the constraint name. If you don’t the system will generate a random constraint name instead. This can wreak havoc because this default constraint name will not be the same name generated on your dev / stage / production systems. This is why it’s always best to specify the name.

Add Default Value to Existing Column

[cc lang=”sql”]
— Add default to existing column DateOfHire:
ALTER TABLE [dbo].[Employees] ADD CONSTRAINT DF_Employees_DateOfHire DEFAULT (GETDATE()) FOR [DateOfHire]

— Add default value to existing column IsTerminated
ALTER TABLE [dbo].[Employees] ADD CONSTRAINT DF_Employees_IsTerminated DEFAULT (0) FOR [IsTerminated]
[/cc]

Add New Column with Default Value

[cc lang=”sql”]
— Add new column DateOfHire with default
ALTER TABLE Employees ADD DateOfHire datetime CONSTRAINT DF_Employees_DateOfHire DEFAULT (GETDATE())

— Add new column IsTerminated with default (no constraint name specified NOT RECOMMENDED)
ALTER TABLE Employees ADD IsTerminated datetime DEFAULT (0)
[/cc]

Add Default Value with Create Table

[cc lang=”sql”]
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 CONSTRAINT DF_Employees_IsTerminated DEFAULT (0) ,
— Add default of getdate()
[DateAdded] [datetime] NULL CONSTRAINT DF_Employees_DateAdded DEFAULT (getdate()),
[Comments] [varchar](255) NULL,
[DateOfHire] [datetime] NULL
)
[/cc]