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 AdventureWorks;
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)