Add non-nullable columns to an existing table in SQL server?

I already have a table which consists of data. I need to alter the table to add two new columns which are not null. How can I do that without losing any existing data?

Here's what I tried (via right-clicking the table and selecting Design):

1. Added new columns 'EmpFlag' (bit, null), 'CreatedDate' (datetime, null)

2. Updated 'EmpFlag' column in the table, to have some valid values. (Just wanted to work on one field, so I didn't update 'CreatedDate' field)

3. Now right clicked table, design, and made it not null.

When I tried to save, this error message appeared:

Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created.

You just set a default value in the new columns and that will allow you to add them.

alter table table_name
    add column_name datetime not null
       constraint DF_Default_Object_Name default (getdate())

or this one for a varchar field.

alter table table_name
    add column_name varchar(10) not null
       constraint DF_Default_Object_Name default ('A')

You can also drop the default if you do not need it after you added the column.

alter table table_name
    drop constraint DF_Default_Object_Name

If you don't want to place a default on the columns, you can:

1. create the new columns as NULLable
2. UPDATE the existing data appropriately
3. add the NOT NULL constraint

Adding new NOT NULL columns with defaults can be done in the GUI like below. Changing an existing to NOT NULL appears to be a different story. I get the same message you had. One option would be to create a new NOT NULL column with a default to replace the old column and then copy the old column data to the new column data.

1. Put table in Design View (right click on table->select Design)
2. Add column, select data type
3. Uncheck Allow Nulls and set Default Value or Binding = your default values like below