Adding a DEFAULT constraint to an existing column

• DEFAULT Constraint can be added when the table is created, as part of the table definition.
• DEFAULT Constraint can be added to an existing table

How to CREATE DEFAULT Constraint on existing table?

To explain this, First I will create a table and then inserts a record (value into the first column and NULL into second column).

CREATE TABLE dbo.TestDefault(

column1 [int],

column2 [int]
)
GO
INSERT INTO dbo.TestDefault (column1) VALUES (10)
GO

Now I will add a DEFAULT constraint to the second column using below T-SQL code:

ALTER TABLE dbo.TestDefault

ADD CONSTRAINT TestDefault_DC_Columns2

DEFAULT 99 FOR column2
GO

To verify whether the default is applied or not, another value is inserted into the first column and the table is queried:
INSERT INTO dbo.TestDefault (column1) VALUES (20)
GO
SELECT * FROM TestDefault
GO