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

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?

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;

As an alternative to the answers listed there is another syntax available which may suit some people better, for example if you use tools like 'ReadyRoll'.

ALTER TABLE [dbo].[FOO] ADD BAR bit NOT NULL CONSTRAINT [df_Bar] DEFAULT 0

Check out this SO answer to see why naming your constraints (the df_Bar above) is nice.

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

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

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.

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

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