50

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.

Improve this question
2
  • I think that is a limitation of SSMS design tool. Try to do it with a SQL statement: "alter table tablename alter column EmpFlag bit not null". Just run a new query with this, and change the table name with the real name. Commented Feb 15, 2010 at 21:21
  • 5
    This has little to do with the specifics of the change you made, but rather reflects the new default behavior in SSMS - to prevent people from unknowingly making disastrous changes to large production tables that will essentially take them offline for the time it takes to copy the data and re-populate the table. You can turn this option off Tools > Options > Designers > Table + DB Designers > Prevent saving changes ... (uncheck this). However be very careful about making these changes to production tables. Commented Feb 19, 2010 at 21:45

3 Answers 3

Reset to default
79

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
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Note that if you have a 100% fill factor (the default), then adding data to all rows like this will go cause a bunch of page splits. This is a lot of I/Os, and I believe this is a blocking operation. This is fine on a moderately small table, but if you have a large and high throughput table it makes it basically an offline operation. To my knowledge, the only way to do this as an "online" operation is to create a new table with the desired structure; merge the data with triggers and batch operations; and finally swap the new table into place in a single transaction (synonyms work well)
This answer worked fine for me, but then I realised I could have done exactly the same within the Design Table screen in SSMS - add a non-nullable column, and in the Column Properties set a Default Value or Binding, then save the changes, and if necessary, remove the Default Value or Binding afterwards
I've struggled with this for years and always wrote SQL code to recreate the table and populated the data. I had no idea it was this simple. Many hours of my life have been wasted :-@
27

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
Improve this answer

2 Comments

In general I agree, but it depends on the scenario. If you are adding columns like CreatedDate and ModifiedDate, you can apply the defaults and then go back and adjust the old data if you want, then you don't have to worry about any downtime (or new rows that slip in) between 2 and 3. In cases where a standard default makes sense, this gives you the flexibility to not synchronize all of the code that doesn't yet know about the new column(s).
This was useful for step 3 - Altering a column: null to not null
2

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

enter image description here

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.