SQL 2005

SQL Server Default Column Values

The eleventh part of the SQL Server Programming Fundamentals tutorial considers the use of default column values. These are literal values or functions that are used to provide default values for columns when new table rows are created.

What is a Default Value?

We have seen in this tutorial that columns can be defined as nullable or not nullable. So far, if a column has been required, it must also have been included in the column list when inserting a new row. This is because any columns omitted from the insert statement are set to null by default. If the column cannot hold a null value, attempting to insert one will simply cause an error.

A default constraint can be applied to a column to give it a default value when a row is created. The default value is generally either a literal value, such as a specific number or string, or a reference to a function. Functions are popular for inserting the current date and time or for generating new globally unique identifiers (GUIDs).

Default values can be assigned to nullable and required columns. In the case of a nullable column, the default value will be used when an insert occurs and the column name is excluded from the statement. To insert a null value, the column must be included in the list and the value of null must be inserted explicitly.

Default constraints are also used when adding a column to a table that already contains data. In this situation, it is not possible to add a non-nullable column to the table unless a default value is specified. The default value is applied to all of the existing rows in the table.

Adding a Default Value to a Column

Default constraints can be added to tables using various methods. In this article we will concentrate on two options: adding default values using the SQL Server Management Studio graphical user interface tools and creating them using Transact-SQL statements.

In the article we will add several default constraints to tables in the JoBS database, which we have created over the course of the tutorial. If you have not followed the tutorial, download and run the scripts using the link at the top of this page to create the database and sample data. You can also use this script if you wish to refresh your copy of the database.

Adding a Default Value Using SQL Server Management Studio

A simple method for adding a default constraint to a table is using the table designer in SQL Server Management Studio (SSMS). Using this tool, a default value can be added during the creation of a new table or by modifying an existing table. We will use this method add a default value to the Engineers table to specify that an engineer will be paid £20 per hour unless otherwise specified.

To begin, locate the Engineers table in the SSMS object explorer. Right-click the table name and choose "Design" to show the table designer. To add the constraint, first you must select the column that you wish to edit. Click the HourlyRate column to select it.

Beneath the design grid is a list of categorised column properties. In this list, you will find a section named "Default Value or Binding". This is where the default value should be entered. In this case, type "20", without the quotation marks, into the corresponding text box. The screen should look similar to the diagram below.

To complete the process, save the updated table and close the table designer window. We can then test the default constraint by opening a new query window for the JoBS database and executing the following insert statement:

INSERT INTO Engineers
    (EngineerId, EngineerName)
VALUES
    (26, 'Timothy McManus')

After running the insert command, review the data in the table. You should see that because no hourly rate was specified for the new engineer, the new row includes a default value of 20 in this column.

Adding a Default Value Using Transact-SQL

There are three common situations where you will want to add a default constraint using T-SQL. The first of these is during the creation of a new table. In this instance, the constraint is provided immediately after the column specification that you wish it to affect. The syntax for a default constraint is as follows:

As the JoBS database is already complete, there is no need to add a new table with a default constraint. However, if we were to recreate the Engineers table including the standard hourly rate described earlier, the T-SQL for the table creation would be as follows:

CREATE TABLE Engineers
(
    EngineerId INT NOT NULL,
    EngineerName VARCHAR(50) NOT NULL,
    HourlyRate MONEY NOT NULL CONSTRAINT DF_Engineers_HourlyRate DEFAULT 20,
    Photograph VARBINARY(MAX)
    CONSTRAINT PK_Engineers PRIMARY KEY CLUSTERED 
    (
        EngineerId
    )
)

NB: In this case, the standard constraint name of DF_Engineers_HourlyRate has been used. This is the name that the table designer would generate for this constraint.

The second situation in which you may create a default constraint is when you are adding a new, non-nullable column to a table with existing data. If you add such a column without a default constraint, as in the following example, you will receive an error message.

In order to add a required column, a default constraint must be included with the column specification. The syntax of the constraint is identical to that used when creating a default value during the creation of a table. The following example adds an overtime rate column to the Engineers table and automatically updates every existing row to use the default value of £30. Try executing the statement then reviewing the data.

ALTER TABLE Engineers ADD OvertimeRate MONEY NOT NULL
CONSTRAINT DF_Engineers_OvertimeRate DEFAULT 30

Finally, you may wish to add a default constraint to an existing table. This uses the ALTER TABLE statement. The statement syntax is similar to that for adding a column specification except that in this case you specify that you wish to add a default constraint. As no column is being added, the previous default constraint syntax must be modified slightly to specify the name of the column being modified. The complete syntax is therefore:

ALTER TABLE table-name
ADD CONSTRAINT constraint-name DEFAULT default-value FOR column-name

We can demonstrate this by adding a new default constraint to the CustomerComplaints table. In this table we want the creation date and time for each row to be recorded. Rather than specifying a literal date value, we will use a function to determine the value for each new row. The function to retrieve the current date and time is getdate().

ALTER TABLE CustomerComplaints
ADD CONSTRAINT DF_CustomerComplaints_ComplaintTime DEFAULT getdate()
FOR ComplaintTime

Once the above constraint is added, the creation time will be recorded automatically for all new complaints. Try adding a row to the table using the following command and then review the data to see the effect.

INSERT INTO CustomerComplaints
    (ComplaintReference, CustomerNumber, Complaint)
VALUES
    (4, 2, 'Bill information is incorrect.')

There are two more default constraints required in the JoBS database. The first of these will set the default customer creation date in the Customers table. In this case, we only want to hold the date of creation, with the time defaulting to midnight. For an explanation of the date processing involved, see the article, "Retrieving the Date From a SQL Server DateTime".

Add the default constraint using the following statement:

ALTER TABLE Customers ADD CONSTRAINT DF_Customers_CreatedDate
DEFAULT convert(DATETIME, floor(convert(FLOAT, getdate()))) FOR CreatedDate

Finally, we will specify that a new, random GUID will be generated as the JobId for each new row in the Jobs table. This will remove the requirement to manually set this table's primary key values. The function to generate a new GUID is named newid().

Dropping a Default Constraint

The final common task to be considered in this article is the deletion of an existing default value. You may need to drop a default constraint if you wish to recreate it with a new value, if you want to drop a column that it refers to, or if you only added the constraint so that you could create a non-nullable column. The syntax for dropping a constraint is as follows:

For example, to drop the default value for the JobId column of the Jobs table, you could execute the following command: