This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(153.6, 55.00000000000001%, 24%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ECS%3C/text%3E%3C/svg%3E)
In MSSMS, I tried to alter the structure of one of my tables (changing a Char(9) to Char(10)) on one of the fields/columns.
It wouldn't let me do it.
But I can do it in Visual Studio's Server Explorer:
I make the change, select the Update button, and voila!
But in MSSMS, I can only get a readonly view of the table structure. Note the process I went through, via these images: there is no "Excecute" or "Update" option available, and when I try to save the changes, I get an err msg. I'm also showing the properties in case that might help some.
How can I "unlock" MSSMS so that I can do more than simply query my tables from there?
In Management Studio, go to menu, Tools, Options, Designers, Table and Database Designers, and try unchecking the “Prevent saving changes that require table re-creation” option. Then save the changes again.
Visual Studio 2019 includes a similar option.
Good day,
Why does Server Explorer in Visual Studio give me more control over my DB than MSSMS does?
Well... the short answer is: since the developers of these external tools designed their tool to provide these options
and the longer answer is:
This is not related to SQL Server. Both SSMS and Visual Studio are external tools developed by different teams according to what they decided that is useful for their audience. SSMS is point for DBAa while Visual Studio is pointing the developers.
If you have feedback regarding a feature which you want that Microsoft developers will add to specific tool then you can send them feedback. To send feedback to the SSMS team you can use this link. For more information on how to send feedback to the VS team check this link.
I don't understand how it's not related to SQL Server.
That aside, though, it is strange to me that a "Management Studio" product would not have a feature available in an "Explorer" product.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(73.60000000000001, 21%, 17%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EDB%3C/text%3E%3C/svg%3E)
This is a safety mechanism to prevent changes that could take a table offline. SSMS has this safety because it is often used for production databases, while Visual Studio very rarely is. As @Viorel points out, you can change this option in SSMS.
Hi,
I don't understand how it's not related to SQL Server.
I meant that this is related to external tools which are not part of the SQL Server
That aside, though, it is strange to me that a "Management Studio" product would not have a feature available in an "Explorer" product.
I have no idea what is "Explorer" product
Before you mentioned 2 products: "SSMS" and "Visual Studio" - These products are not exactly developed by the same team (even if they share some code and point of view) and each team make his own decision about which feature to add
This is not really correct. While SSMS and SSDT (the component in VS that has its table designer) are not part of the SQL Server engine, both are developed and maintained by the SQL Server team.
So Clay's question is certainly on-topic for an SQL Server forum.
I did not meant that it is not fit for SQL Server forums but that the product is not part of SQL Server product but external product
I still disagree with that. The tools team is an integral part of the SQL Server team.
The answer is that the table designer in Visual Studio is a much more modern product than the Table Designer in SSMS which is a piece of total junk.
The particular change you were trying to make is a simple ALTER statement:
ALTER TABLE tbl ALTER COLUMN col char(10)
Well, simple and simple - since it is a fixed data type, this is a size-of-data operation. But still, it is a single statement.
BUT THAT IS NOT WHAT SSMS WILL EXECUTE! Viorel gave you a very bad advice. That setting is there for a reason, and the reason is that the Table Designer in SSMS is awful.
You see, SSMS will only use ALTER TABLE when this is supported on SQL Server 6.5. Yes, you read correctly. SQL Server 6.5. So while it looks like a simple point-and-click operation, SSMS will create a new table under the hood and copy data over. And the script it generates has several issues. The developers who implemented it originally very apparently had very little understanding of what it means to make schema changes to tables.
Stay away, stay away and stick to Visual Studio. Also VS will create a new table and copy data over, but only when it has to, for instance when you add a column in the middle. But I believe that the script that VS generates is more robust. (It was a while since I looked at it.)