The changes you have made require the following tables to be dropped and re-created

19 Comments

• Don't try this on a table that contains millions of records or a live database.
  

• Thanks for the info. Certainly this should be only done during development phase in an empty table.
  

• Thank you very much.
  

• Thanks, I've been facing this problem quite a lot
  

• Thank you. I was just about to drop all my DB tables and try to create the whole DB from scratch ....saved my life!
  

• Thank you very much.Solved my problem.
  

• You saved my from blowing a deadline on an active directory project with 25,000 users! Thank you so much for taking a moment to share.
  

• I've read similar posts on a dozen different websites, but none are addressing the most important concern.
  
  I need to add a column to a table contain a few million rows. After testing this solution on a test database, I find that it only takes about 30 seconds to complete; not a big deal.
  
  So is this safe to do on a production table after hours?
  
  Is there any risk of losing data of any kind (table data, constraints, stats, indexes)?
  
  I guess I don't understand why MS locks you out in the first place. Is it solely because of the potential of hanging a production database for a long period of time (because its such an extensive operation)?
  
  Thanks for any insight!
  

• Thanks for question, Andy. There is reason why MS locks this feature by default. Re-creating large tables may take more time than couple of seconds, it may take even hours to make change like this on large tables. The way how Management Studio performs this operation is very costy.
  

• So it has nothing to do with data loss, purely a consideration because of the intensity of the operation?
  

• This far I have done this operation without data losses. If tables are large then it may take long hours to recreate them and fill with data again.
  

• thanks for the information, that is very helpful one.. cheers!!!
  

• Thanks a lot. Saved me my day. I underwent the pain of dropping and re-creating the entire DB (development) a week earlier. Whew!!! An intimation along the "Save Cancelled" dialog box would have been a better option. Seems Microsoft had left its SQL users running helter-skelter by hiding such control info. Also makes an impression that Microsoft's curve bends low towards user-friendliness and goes soaring towards sophistication. While it seems good, not to forget the huge base of confused users left behind.
  

• Thank you very much. I was facing this problem since a long time. Your blog post solved it.
  

• thanks! i thought it was a server setting!
  

• Thanks a lot
  

• good info... thanx
  

• Thank you so much!!!saved my time..:)
  

• Thanks... hate MS so much.. forced to work with this terrible DBMS for my current project.