SQL Server – Simple Tip On Altering SQL Server Tables With Data – Avoid “Saving changes not permitted”

This is a very simple yet handy tip that most SQL Server users are not aware of.

Can’t alter tables with data?!

If you use SQL Server, you must have seen this dialog when you try to alter a table in SSMS (like adding a new column to an existing table containing data).

SSMS_Save_Not_Permitted

What does it mean? The fix:

All this is saying is that it has to recreate the table and reinsert the data. Although SSMS can do this for you, it just does not offer any indications that it can. If you understand the risks, you can bypass this check and have SSMS do the drop followed by recreate by changing the following setting:

Tools -> Options -> Designers -> Table and Database Designers -> Uncheck “Prevent saving changes that require the table to be re-created”

On small tables, data is dropped and recreated without warning after the above change.

More bad things CAN happen

On large tables, you might get a warning such as this one:

‘<TableName> (<SchemaName>)’ table

– Saving Definition Changes to tables with large amounts of data could take a considerable amount of time. While changes are being saved, table data will not be accessible.

..and later, it might still fail with a message such as this one

‘<TableName> (<SchemaName>)’ table

– Unable to modify table. 

Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

Please use caution as this has several negative consequences when used on large tables and always backup before you attempt any big changes.

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s