Defaults
Defaults specify what values are used in a column if you do not specify a value for the column when inserting a row. Defaults can be anything that evaluates to a constant, such as:
- Constant
- Built-in function
- Mathematical expression
There are two ways to apply defaults:
- Create a default definition using the DEFAULT keyword in CREATE TABLE to assign a constant expression as a default on a column.
This is the preferred, standard method. It is also the more concise way to specify a default.
- Create a default object using the CREATE DEFAULT statement and bind it to columns using the sp_bindefault system stored procedure.
This is a backward compatibility feature.
This example creates a table using one of each type of default. It creates a default object to assign a default to one column, and binds the default object to the column. It then does a test insert without specifying values for the columns with defaults and retrieves the test row to verify the defaults were applied.
USE pubs
GO
CREATE TABLE test_defaults
(keycol smallint,
process_id smallint DEFAULT @@SPID, --Preferred default definition
date_ins datetime DEFAULT getdate(), --Preferred default definition
mathcol smallint DEFAULT 10 * 2, --Preferred default definition
char1 char(3),
char2 char(3) DEFAULT 'xyz') --Preferred default definition
GO
/* Illustration only, use DEFAULT definitions instead.*/
CREATE DEFAULT abc_const AS 'abc'
GO
sp_bindefault abc_const, 'test_defaults.char1'
GO
INSERT INTO test_defaults(keycol) VALUES (1)
GO
SELECT * FROM test_defaults
GO
The output of this sample is:
Default bound to column.
(1 row(s) affected)
keycol process_id date_ins mathcol char1 char2
------ ---------- --------------------------- ------- ----- -----
1 7 Oct 16 1997 8:34PM 20 abc xyz
(1 row(s) affected)