I already have a table which consists of data. I need to alter table to add two new columns which are not null. How can i do that without loosing any existing data?
NOTE: The new columns are not null fields.
+11
A:
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
2010-02-15 19:53:36
This answer is correct.
mwigdahl
2010-02-15 19:54:41
you should provide some sample code
Manu
2010-02-15 19:54:41
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)
2010-02-16 20:43:03
+4
A:
If you don't want to place a default on the columns, you can:
- create the new columns as NULLable
- UPDATE the existing data appropriately
- add the NOT NULL constraint
Aaron Bertrand
2010-02-15 19:55:59
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
2010-02-15 20:23:52
A:
Alrite this is what i did.
Right click table and clicked on 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 tried to save, this error message apeared:
"Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. ......."
Any suggestion?
sanjeev40084
2010-02-15 21:04:30
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
2010-02-15 21:21:59
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
2010-02-19 21:45:12