Home > Management Studio, SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2, SQLServer 2012, Working With Data > SQL Server – Saving Changes Not Permitted in Management Studio

SQL Server – Saving Changes Not Permitted in Management Studio

SQL Server Management Studio does not allow you to save changes to a table which require table re-creation such as changing data type for a column. When you perform such changes you will run into following error message:

image

Here, I have tried changing data type for 'Name' column from NVARCHAR(50) to CHAR(50). Since this requires table re-creation Management Studio does not allow this operation to be completed, and it is doing so for your own good!

This activity can be completed without re-creating table if you are using T-SQL. Following T-SQL code can be used to avoid table re-creation:

USE [SqlAndMe]

GO

 

ALTER TABLE [dbo].[Customers]

ALTER COLUMN Name CHAR(50)

GO

The above statement will change the data type of the 'Name' column to CHAR(50) without having to re-create table.

We are better off with this option enabled. Although, if you are a GUI fan you can disable this safety net.

1. Go to Tools > Options

2. Go to Designers > Table and Database Designers

3. Uncheck Prevent saving changes that require table re-creation.

image

You can find more information on why not to disable this option here:

SQL Server – ALTER COLUMN – Management Studio v. T-SQL

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

About these ads
  1. April 11, 2013 at 7:34 pm

    Do you have any links that explain WHY SSMS forces the table to be recreated? I found this article:

    http://stackoverflow.com/questions/8668809/why-does-sql-server-force-me-to-drop-the-table-to-be-able-to-alter-a-field-from

    Thanks

  1. No trackbacks yet.

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

Follow

Get every new post delivered to your Inbox.

Join 279 other followers

%d bloggers like this: