[sql] Alter column in SQL Server

What is correct syntax for an ALTER statement to add a default value to an existing column?

I can add new column with no errors:

ALTER TABLE tb_TableName ADD Record_Status varchar(20)

But If I try to alter existing column to apply default value by using the following statement:

ALTER TABLE tb_TableName 
ALTER COLUMN Record_Status VARCHAR(20) NOT NULL DEFAULT ''

or

ALTER TABLE tb_TableName 
ALTER Record_Status VARCHAR(20) NOT NULL DEFAULT ''

I have get an error:

Incorrect syntax near 'Record_Status'.

This question is related to sql sql-server-2008

The answer is


Try this one.

ALTER TABLE tb_TableName
ALTER COLUMN Record_Status VARCHAR(20) NOT NULL

ALTER TABLE tb_TableName
ADD CONSTRAINT DEF_Name DEFAULT '' FOR Record_Status

To set a default value to a column, try this:

ALTER TABLE tb_TableName
ALTER COLUMN Record_Status SET DEFAULT 'default value'