Transact-SQL Topics: The Default Value of a Column

	The Default Value of a Column

Description

A default value is one that a column would apply to its record if a value is not provided. You can assign a default value when creating a table in Design View or programmatically.

To visually specify the default value of a column, in the top section of the table in Design View, click the column. In the bottom section, click Default Value or Binding, type the desired value following the rules of the column's data type.

To specify the default value in a SQL statement, when creating the column, before the semi-colon or the closing parenthesis of the last column, assign the desired value to the DEFAULT keyword. Here are examples:

CREATE TABLE Employees
(
    FullName NVARCHAR(50),
    Address NVARCHAR(80),
    City NVARCHAR(40),
    State NVARCHAR(40) DEFAULT N'NSW',
    PostalCode NVARCHAR(4) DEFAULT N'2000',
    Country NVARCHAR(20) DEFAULT N'Australia'
);
GO

The default value can also come from a function. Here is an example:

CREATE TABLE Employees
(
    EmployeeName nvarchar(50),
    DateHired date default GETDATE(),
    Address nvarchar(50),
    City nvarchar(40),
    State nchar(2) DEFAULT 'VA',
    PostalCode NVARCHAR(4),
    Country NVARCHAR(20)
);
GO

If the table exists already and you want to add a column that has a default value, use the formula:

ALTER TABLE TableName ADD ColumnName Options

Here is an example:

USE Exercise1;
GO

ALTER TABLE Employees
ADD HomePhone nvarchar(20) default N'(000) 000-0000';
GO

After creating the table, the user does not have to provide a value for a column that has a default. If the user does not provide the value, the default would be used when the record is saved.