question

You need to first create the default constraint on its own, then bind the constraint to the column. The only time DEFAULT 0 works is on CREATE not ALTER

If the column RejectReson did not exist yet then you could add the column and its respective default specification at the same time as @anthony.green already pointed out in his answer. When adding a NULLable column to existing table and yet requiring that all existing rows to have the default value already set for this new column then WITH VALUES can be specified, i.e. alter table enquiry add RejectReson int null default(0) with values; go The statement above would add new NULLable column and populate all existing rows with default value of 0 for this new column. If the newly created column mustn't allow NULL then WITH VALUES is not necessary as adding the default while creating NOT NULLable column will do the trick, i.e. all existing rows will come in populated with default value: alter table enquiry add RejectReson int not null default(0); go However, because the column already exist (I presume it is NULLable), adding the default to it ***will not*** automatically populate existing rows with default value for this existing column. Adding default will affect the rows which will be inserted ***in the future***. Coming back to the original question: the syntax is incorrect, and also there is no way to add a default to ***existing*** NULLable column and force all rows to be automatically updated. In order to achieve the desired result, please run the following script: alter table enquiry add default(0) for RejectReson; go update enquiry set RejectReson = 0; go Now, the default has been added, all existing rows have default value, the column is NULLable, and yet all newly inserted rows will come in with default value (unless explicitly specified otherwise). Hope this helps. Oleg