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:

• 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)