Problem solve Get help with specific problems with your technologies, process and projects.

Warned about table changes in SSMS? Here’s a workaround

Editing tables in SQL Server Management Studio and getting this warning, “Saving changes is not permitted”? It happens when the table needs to be re-created. But don’t fret. This change in SQL Server 2008 R2 can be undone with a simple tweak.

I use SQL Server Management Studio to directly edit the structure of the SQL Server databases I work on. I’m not...

too worried about this causing damage to a production database, since I try to confine my work to tables that are specifically not exposed on production servers -- or, at the very least, I perform edits on live tables in such a way that the changes are additive, not destructive. From the point of view of database design, it’s always easier to add a new column than drop an existing one. There’s far less chance the schema you’re creating will be trashed.

That said, when Microsoft released SQL Server Management Studio 2008 R2, the company took steps to protect people’s data designs. I found, however, like some protective measures it has made to other products, it wasn’t always helpful. One of the new changes specifically relates to editing tables. If you edit a table and a drop and re-create is required, SQL Server Management Studio gives you a warning:

“Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created.”

This is followed by a list of whatever tables are affected, which you can then save to a text file for later reference. But odds are the list simply contains the table you were working on.

The types of changes in question are typically not that complex. I’ve tripped up this warning by trying to change the “Allow nulls” option for a column. This seems like a simple little change, but it forces the system to make sure there are no null values assigned to that column throughout the table. In truth, these are pretty normative changes, but in the past it was easy to think nothing of them since Management Studio lets you make such changes without blinking.

If you don’t think you’ll put yourself in a situation in which having a table automatically dropped and re-created will be a problem, you can disable this warning. Go to Tools | Options in Management Studio, select “Designers”, uncheck the box labeled “Prevent saving changes that require table re-creation,” and click “OK.” No restart of the program should be required.

Uncheck “Prevent saving changes that require table re-creation” to allow SQL Server Management Studio 2008 R2 to enact changes to tables that require them to be dropped and recreated. Be warned that rebuilding large tables this way can cause performance issues.

Others have pointed out that another reason this option was put into place was because of the time required to make changes to large tables; that can cause the database to stop responding. Your definition of “large” is going to vary based on your usage scenario and hardware. My tables typically have been in the range of a few dozen rows to several thousand, with a few up in the hundreds of thousands. For tables with millions of rows or more, any changes that force a drop and re-create ought to be done as part of a planned maintenance window.

Serdar Yegulalp has been writing about computers and IT for more than 15 years for a variety of publications, including InformationWeek and Windows Magazine.

Dig Deeper on SQL Server Database Modeling and Design