+2 votes
1 view
in SQL by (40.4k points)

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

1 Answer

+5 votes
by (40.3k points)
edited by

Syntax:

ALTER TABLE {Table_Name}

ADD {Column_Name} {NOT NULL|NULL}

CONSTRAINT {Constraint_Name} DEFAULT {Default_Value}

WITH Values

Have a look at this video on ALTER TABLE STATEMENT in SQL

Let’s understand this via an example:

 ALTER TABLE TableA

ADD Column1 Bit NULL --Or you can also use NOT NULL

CONSTRAINT D_TableA_Column1 --when the Constraint is deleted a default constraint name will be generated automatically

DEFAULT (0) --Default constraint, not mandatory to use.

WITH values --If you want to add a default value for existing records and column is nullable then add this.

Note:

When you use optional With-Values Statement:

The WITH VALUES is only needed if you want to add a default value for existing records and column is nullable then add this. It will automatically use the default Value for all existing records, whether you specify WITH VALUES or not if the Column is NOT NULL.

You can refer to this video to understand how CONSTRAINTS works.

When you use Optional Constraint Name:

If you’ll not use CONSTRAINT D_TableA_Column1 then SQL Server will generate a Default-Constraint automatically with a different Name like: DF__TableATa__Column1C__4FB7FEF6

Let’s understand how Inserts work with a Default-Constraint: If you insert a Record  into TableA and do not Specify Column1’s value, then it will Default to 0. But, 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.

Welcome to Intellipaat Community. Get your technical queries answered by top developers !


Categories

...