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
(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.
27 August 2008