Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All


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;
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;
/* For illustration only, use DEFAULT definitions instead.*/
CREATE DEFAULT abc_const AS 'abc';
sp_bindefault abc_const, 'test_defaults.char1';
INSERT INTO test_defaults(keycol) VALUES (1);
SELECT * FROM test_defaults;

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)
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

© 2015 Microsoft