Categories
sql sql-server sql-server-2000 sql-server-2005

Add a column with a default value to an existing table in SQL Server

3109

How can I add a column with a default value to an existing table in SQL Server 2000 / SQL Server 2005?

3

  • ALTER TABLE SomeTable ADD SomeCol Bit NULL –Or NOT NULL. CONSTRAINT D_SomeTable_SomeCol –When Omitted a Default-Constraint Name is autogenerated. DEFAULT (0)–Optional Default-Constraint. WITH VALUES –Add if Column is Nullable and you want the Default Value for Existing Records.

    Aug 3, 2021 at 17:06

  • 1

    @MichaelZ. – 43 answers now …..

    – Alex

    Dec 7, 2021 at 12:49

  • 1

    I think this question does not meet the minimum effort requirements and should have been removed, of course. @MichaelZ.

    Apr 22 at 17:19

3921

+50

Syntax:

ALTER TABLE {TABLENAME} 
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} 
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
WITH VALUES

Example:

ALTER TABLE SomeTable
        ADD SomeCol Bit NULL --Or NOT NULL.
 CONSTRAINT D_SomeTable_SomeCol --When Omitted a Default-Constraint Name is autogenerated.
    DEFAULT (0)--Optional Default-Constraint.
WITH VALUES --Add if Column is Nullable and you want the Default Value for Existing Records.

Notes:

Optional Constraint Name:
If you leave out CONSTRAINT D_SomeTable_SomeCol then SQL Server will autogenerate
    a Default-Contraint with a funny Name like: DF__SomeTa__SomeC__4FB7FEF6

Optional With-Values Statement:
The WITH VALUES is only needed when your Column is Nullable
    and you want the Default Value used for Existing Records.
If your Column is NOT NULL, then it will automatically use the Default Value
    for all Existing Records, whether you specify WITH VALUES or not.

How Inserts work with a Default-Constraint:
If you insert a Record into SomeTable and do not Specify SomeCol‘s value, then it will Default to 0.
If you insert a Record and Specify SomeCol‘s value as NULL (and your column allows nulls),
    then the Default-Constraint will not be used and NULL will be inserted as the Value.

Notes were based on everyone’s great feedback below.
Special Thanks to:
    @Yatrix, @WalterStabosz, @YahooSerious, and @StackMan for their Comments.

5

  • 355

    Keep in mind that if the column is nullable, then null will be the value used for existing rows.

    Jan 31, 2012 at 15:43

  • 20

    @Thecrocodilehunter Nullable column means that you can insert Null for the columns value. If it’s not a nullable column, you have to insert some value of that data type. So, for existing records, Null will be inserted in them and in new records, your default value will be inserted unless otherwise specified. Make sense?

    – Yatrix

    Feb 29, 2012 at 16:42


  • 46

    I like this answer a little better than dbugger’s because it explicitly names the default constraint. A default constraint is still created using dbugger’s syntax, except its name is auto-generated. Knowing the exact name is handy when writing DROP-CREATE scripts.

    Mar 23, 2012 at 12:43

  • 51

    Use WITH VALUES to update existing nullable rows. See MSDN: “If the added column allows null values and WITH VALUES is specified, the default value is stored in the new column, added to existing rows.”

    Jul 28, 2014 at 9:45


  • When it back-fills the default value, will it fire update triggers and/or take table locks? If so, can they be avoided?

    – xr280xr

    Dec 2, 2021 at 23:01

1136

ALTER TABLE Protocols
ADD ProtocolTypeID int NOT NULL DEFAULT(1)
GO

The inclusion of the DEFAULT fills the column in existing rows with the default value, so the NOT NULL constraint is not violated.

1

  • 94

    Just to clarify – if “NOT NULL” is omitted from the command, the value for existing rows will NOT be updated and will remain NULL. If “NOT NULL” is included in the command, the value for existing rows WILL be updated to match the default.

    – Stack Man

    Aug 14, 2012 at 22:11


270

When adding a nullable column, WITH VALUES will ensure that the specific DEFAULT value is applied to existing rows:

ALTER TABLE table
ADD column BIT     -- Demonstration with NULL-able column added
CONSTRAINT Constraint_name DEFAULT 0 WITH VALUES

0