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:
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:
ALTER TABLE [dbo].[Customers]
ALTER COLUMN Name CHAR(50)
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.
You can find more information on why not to disable this option here:
Hope This Helps!
- SQL Server – Displaying line numbers in Query Editor – SSMS
- SQL Server – Difference between @@CONNECTIONS and @@MAX_CONNECTIONS
- SQL Server – Different ways to check Recovery Model of a database
- SQL Server – Calculating elapsed time from DATETIME
- SQL Server – Kill all sessions using database
- SQL Server – Custom sorting in ORDER BY clause
- SQL Server – Script to get Service Account for all local instances
- SQL Server – Get SQL Server Service Account using T-SQL
- SQL Server – Import text file using xp_cmdshell
- SQL Server – Get values as DATETIME from sysjobhistory
This is a personal weblog. The opinions expressed here are my own and not of my employer. For accuracy and official references refer to MSDN, Microsoft TechNet, Books Online. I or my employer do not endorse any of the tools / applications / books / concepts mentioned here on my blog. I have simply documented my personal experiences on this blog.
- Backup & Recovery (11)
- Catalog Views (23)
- Certification (1)
- Common Table Expressions (6)
- Database Mail (1)
- Management Studio (37)
- Management Views and Functions (11)
- Partitioning (3)
- Service Pack Releases (2)
- SQL Agent (7)
- SQL Bugs (2)
- SQL Configuration (30)
- SQLServer (161)
- Uncategorized (1)
- Undocumented Functions (21)
- Working With Data (14)