48

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.

Tony L.
  • 17,638
  • 8
  • 69
  • 66
sanjeev40084
  • 9,227
  • 18
  • 67
  • 99
  • 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. – Jose Chama Feb 15 '10 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. – Aaron Bertrand Feb 19 '10 at 21:45

3 Answers3

76

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
Jose Chama
  • 2,948
  • 17
  • 22
  • 2
    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) – ahains Feb 16 '10 at 20:43
  • 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 – MarkD Dec 11 '13 at 12:10
  • 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 :-@ – Drew Chapin May 04 '17 at 17:08
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
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • 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). – Aaron Bertrand Feb 15 '10 at 20:23
  • 1
    This was useful for step 3 - [Altering a column: null to not null](https://stackoverflow.com/q/689746/188926) – Dunc Jun 26 '17 at 11:07
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

Tony L.
  • 17,638
  • 8
  • 69
  • 66