I just got a new computer and reinstalled SQL Server Management Studio (both 2008 and 2012), and I was hit with a little annoying data security feature which I previously disabled and forgot about a long time ago. A dialog telling me that SSMS could not save changes to a table because it had to be dropped and re-created.
So, the issue is when making changes to a table design, and there is data in the table, SQL Server will put the data in a temp table, drop the table, and recreate it with the changes, and then reinsert the data.
If you are changing an existing table, with potentially thousands of record, then I could see the benefit of keeping this feature on, not only will it take a long time to save the changes (because it will have to backup all the records, then reinsert them), but it protects you from accidentally corrupting your data, or making changes if you don’t know what you are doing. However, if you are a developer, and you are building a new app, and need to tweak your DB design, then this can get annoying.
Enable Saving changes in SQL Server
Tools -> Options -> ‘Designers’ tab
-> Uncheck ‘ Prevent saving changes that require table re-creation’
Problem Fixed!
