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

I have also done it as you say "create it as nullable, do an insert than switch it non-nullable". I've not had a problem doing it this way.

I've not yet needed to find a better way, however I'm intrigued if there is another way....

Answer 2

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;

Answer 3

I usually create the field as nullable with a default as false in your case update all the fields that were in the database prior then switch it to null

Answer 4

ALTER TABLE dbo.MyTable ADD MyColumn bit NOT NULL DEFAULT 0

For what it is worth, you can fire up Enterprise Manager, make the changes in the UI, and then have it generate a Change Script - and you can see how it would accomplish these kinds of tasks.

Answer 5

ALTER TABLE foo ADD bar bit DEFAULT 0 NOT NULL WITH VALUES;

The "with values" clause propigates the default value into existing rows.