Why Management Studio forces you to know SQL

Suppose you have a big table with about 3 GB of data in and you need to add a nullable new column on it, you can open SSMS, open the table in designer and create a new column of type Int called sclo_durationInMinutes, press save and the table gets update quite immediately.

Then you realize that the user want the duration in Minutes as a floating point number, so you open the designer, change the type of the sclo_durationInMinutes from Int to Float and press save…. after 30 seconds SSMS tells you that you got a timeout. If you look at the change script you can verify, with HORROR, that changing the type of a column is done with the creation of a temporary table, copy all the data (3 GB) in the temp table and finally deleting the old table and renaming the temp table with the name of the original table… REALLY?

Then I simply opened a new query and issue an ALTER TABLE manually

alter table dbo.scanlog

alter column sclo_durationInMinutes Float null 

I know that SSMS should work for a wide range of change, but requiring a full copy of a table just to change the type of a column it is quite overkill.

In the end, if you use SSMS it is better for you to have a deep knowledge of SQL, because the designer too much often opts for Copy-drop-rename strategy to alter structure of a table.

Gian Maria.

Published by

Ricci Gian Maria

.Net programmer, User group and community enthusiast, programmer - aspiring architect - and guitar player :). Visual Studio ALM MVP

3 thoughts on “Why Management Studio forces you to know SQL”

  1. The default behaviour of SSMS is to block any change that would require a rebuild of the table (default value of the flag ‘Prevent saving changes that require table re-creation’ under Tools->Options->Designers is ‘true’), so if you don’t change that flag you have to use the ALTER statement as in your example.

    But i understand your point, and i totally agree with it :) SSMS should choose by itself the better way to save a change in a table :)

  2. Yes, you are right ;) but that flag is the first one I remove from the options :) because it is really annoying :). Database projects in Visual Studio have refactoring capabilities that issue an ALTER TABLE to rename column, so I’d like SSMS to use similar “smart” behavior.

    Denali tools seems to be an improvement over SSMS, so I hope in a brighter future :)


  3. I know what you mean, i HATE that flag too. Denali tools are very powerful, but i think i’ll use SSMS for a long time. I hope for an integration of the new tools capabilities into SSMS – one day :)

    The big problem i see, if you aren’t aware of that and don’t use the ALTER TABLE approach, is about the RECREATION of FILESTREAM-enabled tables caused by a simple change in table schema (like the one you pointed out in your post)…such a REALLY BIG pain ^_^

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.