.png)
Defaults
Defaults specify what values are used in a column if you do not specify a value for the column when you insert a row. Defaults can be anything that evaluates to a constant, such as a constant, built-in function, or mathematical expression.
To apply defaults, create a default definition by using the DEFAULT keyword in CREATE TABLE. This assigns a constant expression as a default on a column. For more information, see Creating and Modifying DEFAULT Definitions.
The following example creates a table that uses different types of default expressions. 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 AdventureWorks2008R2; 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 /* For 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
Here is the result set.
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)
