Set default value for column of an existing table

Question

It is possible to set default value for column of an existing table?

I am trying this way, but always the column is null instead of the default value that was reported.

IF NOT EXISTS (SELECT * FROM SYSCOLUMNS C INNER JOIN SYSOBJECTS T ON C.id = T.id WHERE C.name = ('IsPreparacao') AND T.name = 'Cadastro')

BEGIN
ALTER TABLE Cadastro   
    ADD IsPreparacao bit  default 0
END

I’ve searched the net and I haven’t found anything that helps me as my problem, someone can help me with this question?

Thank you so much that you can help me.

Answer 1

Try it this way

BEGIN
ALTER TABLE Cadastro   
        ADD IsPreparacao bit NOT NULL default 0
END

The condition NOT NULL will cause existing records to be filled with the default value. A Constraint (NOT NULL) will be created by default.

If you want to explicitly define the name of Constraint, or your spine may have NULL then you can use the following syntax.

ALTER TABLE Cadastro    
ADD IsPreparacao BIT NULL
CONSTRAINT IsPreparacaoDefault DEFAULT 0
WITH VALUES