Alter column in SQL Server

Question

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'.

Answer 1

I think you want this syntax:

ALTER TABLE tb_TableName   add constraint cnt_Record_Status Default '' for Record_Status 

Based on some of your comments, I am going to guess that you might already have null values in your table which is causing the alter of the column to not null to fail. If that is the case, then you should run an UPDATE first. Your script will be:

update tb_TableName set Record_Status  = '' where Record_Status is null  ALTER TABLE tb_TableName ALTER COLUMN Record_Status VARCHAR(20) NOT NULL  ALTER TABLE tb_TableName ADD CONSTRAINT DEF_Name DEFAULT '' FOR Record_Status 

See SQL Fiddle with demo

Answer 2

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