Why does Server Explorer in Visual Studio give me more control over my DB than MSSMS does?

clay shannon 66 Reputation points
2020-10-10T14:14:02.427+00:00

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:

31384-serverexplorer-opentabledefinition.png

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.

31403-selectdesign.png

31356-mssmssavechanges.png

31199-preventsavingchanges.png

31348-dbproperties.png

How can I "unlock" MSSMS so that I can do more than simply query my tables from there?

SQL Server | Other
0 comments No comments
{count} votes

Accepted answer
  1. Viorel 122.6K Reputation points
    2020-10-10T14:52:57.353+00:00

    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.

    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Ronen Ariely 15,206 Reputation points
    2020-10-10T14:27:51.507+00:00

    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.


  2. Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator
    2020-10-10T19:12:07.047+00:00

    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.)

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.