How do I add a column to a table in SQL Server that doesn't allow nulls?

Question

I have a table that I want to add a bit column, which I wish to default to false for all existing data.

How do I alter my table in such a way that it allows me to specify NOT NULL before I have inserted false for my existing rows?

Should I create it as nullable, do an insert than switch it non-nullable?

Answer 1

You could add the column and provide the default value to be used for all existing rows.

ALTER TABLE foo 
ADD bar bit 
DEFAULT 0 NOT NULL;

How do I add a column to a table in SQL Server that doesn't allow nulls?

Sign up