We have a table in our database with a Reference column. For a long time these reference values only pointed to one system thus several legacy apps assume that if it contains value, they should use it with that system.

We're updating that model to add indication which of the external systems the value refers to.

Unfortunately not all the apps caught up with that change, which is causing problems. So as temporary measure we're planning to store the reference elsewhere.

We have two options:

  1. Add a new column to the table
  2. Add a new table

Once all the apps are up to scratch, we'll copy the values from temp location over to the Reference column and then drop the source.


Which of those two options is less messy and has less negative impact especially when it comes to cleaning it up?


The table currently has 70k rows, 40 columns. Only rows inserted/updated by new apps will have that temporary value.


As per my experience, adding new table (Option 2) referencing index column would be you best choice. And will be less messy and easy to use.

Also, going further you may be able to add columns to the newly created table.

Hope this help!

| improve this answer | |
  • Thanks, we were leaning towards option 2! – Patrick Jun 5 '19 at 19:43

If I understand correctly you are planning to create an additional column/table, copy over data and eventually drop the column or table?

First of all I don't like messing around with tables containing production data. It's always a safer bet to store such temporary data in a separate (staging)table. If a mistake of any order occurs, your production data will stay safe.

When cleaning up the data, your performance and concurrence will be better when dropping a separate table than when dropping a column from a live production table. Dropping a column will require a sch-m lock, your table will be unavailable for reads and writes during this operation. Depending on the size of that table, this can generate lots of log writes and take up a long time. When you drop a separate table, all locking will happen on this table. Your production table will remain available during the whole process.

Good luck! :)

| improve this answer | |
  • Thanks, option 2 seems cleaner, has less impact on the database (locking) and we were leaning towards it – Patrick Jun 5 '19 at 19:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.