SQL: Management Studio not saving changes to table

Having a blog about something you like is a great way to learn more things about it. If you keep an eye open for the other bloggers the term “community” really makes sense. This week while checking the recent activity I came across the blog of another guy who work with some technologies related to what is discussed here.

And because he has a different vision I learned something new. Something new about a tool I use everyday, but never click where he clicked. The original post and problem that triggered the creation of this post is here: http://berniecook.wordpress.com/2013/04/12/saving-changes-is-not-permitted-sql-server

Summing it all up: in certain conditions the SSMS wizard will try to drop and recreate a table when you make some changes to it. And in newer versions this is disabled by default, bringing you to this error message:

 “Saving changes is not permitted. The changes you have made require the
following tables to be dropped and re-created. You have either made
changes to a table that can’t be re-created or enabled the option
Prevent saving changes that require the table to be recreated.” 

This is actually safer than the previous behavior, because it makes you pay more attention to what you are doing. Changing the schema on-the-fly may be a simple thing on NoSQL world, but not on classic relational databases. It makes you pay more attention to this action and I acknowledge this can be irritating in development/QA environments, but better safe than sorry, right?

I posted a comment there pointing the good side of this “safety feature”, and giving the recommendation to use SSMS to create scripts of the actions you want to perform, then review those scripts and execute them only when you are sure it is the right thing to do.

The simplest example (and most frequent, I believe), is changing the datatype of an existing column. When you add a new column SSMS will behave and not drop anything, for example. But when you change the datatype of a column with some rows, SSMS will move all data around just to be sure that it does not truncate some rows inadvertently.

By “moving” I mean: create new table with another name, copy all data, drop original table, recreate indexes, recreate FKs, rename objects, and so on. Seasoned DBAs may shiver when they see a script that does all this to a big table on a live system.

The best way to avoid this is review what you want to do with the table in the first place. If a change to the datatype is really needed, you must be sure the new type will be compatible with existing data and the size will be equal or bigger. Given you obey these conditions, use a single line ALTER statement and be happy. If you want to increase a varchar field from 10 to 20, do this:

alter table your_table alter column your_column varchar(20) 

This is a pretty common question, check this link:  http://stackoverflow.com/questions/11035295/sql-server-management-studio-how-to-change-a-field-type-without-dropping-table . Below is my original comment. The important line is the last one.

Using Management Studio to make changes do tables is a pretty risky thing to do. It will usually drop and recreate objects, and if all your systems and databases are nice to each other this would never be an issue, but sometimes it breaks things. I have seen it fail while in the middle of a change, leaving me with a table with the wrong name and a system halted.

In some cases, this human review is really useful: for example changing the datatype of one column would make SSMS drop and recreate the table, but it is perfectly fine to do this by using an ALTER TABLE command (not to mention the time required to do this if the table is big).

Because I came from the databases world, I usually advise people to review the code that SSMS generates and then execute it – don’t trust blindly in SSMS nice little buttons ;).



About mauriciorpp

Hi, I am Piccolo - but not the one from Dragon Ball. I'm from a highly competitive and fast-paced world too, the IT industry, and this space will be used to share some challenges I face daily on my career. As you will see, I don't do brawls but I need to be a fighter! Stay tuned.
This entry was posted in SQLServer and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s