HEELPBOOK - SQL Server - 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
)




############ ARTICLE INFO #############

Article Month: July
Article Date: 12/07/2012
Permalink: http://heelpbook.altervista.org/2012/sql-server-add-column-default-value/
Source: http://sqlserverplanet.com/ddl/add-column-default-value
Language: English

View more articles on: http://www.heelpbook.net/
Follow us on Facebook: http://it-it.facebook.com/pages/HeelpBook/100790870008832
Follow us on Twitter: https://twitter.com/#!/HeelpBook
Follow us on RSS Feed: http://feeds.feedburner.com/Heelpbook
Follow us on Delicious: http://delicious.com/heelpbook