Russ Thomas – SQL Judo

The Art of SQL Server Database Administration, Development, and Career Skills for the Technically Minded

SSMS Day 11: Working With Designers

31 Days of SSMS: Table of Contents

Today were going to spend some time on the built in designers that come with SSMS and the options that affecting them that any user of SSMS should be aware of.  I’ll also show you how to script those actions instead and talk about why you should.

Using the GUI and Designers

Nearly every node under a database in the object explorer has an impressive list of options when you right click.  One of the most common options you’ll select from the right click menu is ‘New’, when creating objects.  If you right click the table node for example you see the following under New.

new

Many objects will generate a script template when you select ‘new’ from the menu; programmable objects such as functions and stored procedures for example.  An exciting ‘new’ option in SSMS 2014 is the option to generate an In-Memory tables!!    Other objects however, such as when creating a basic table or view will bring up a GUI designer.

It’s important to keep in mind whether you get a script or an actual gui designer – behind the scenes its almost always still a T-Sql statement that will ultimately accomplish what you are attempting to do.  That will become really important to remember here in a minute.

Let’s take a look at one of the most critical designers and the options that affect how it behaves.

Table Designer

I like to script out almost everything I do in SSMS.  Unless it’s a full-on db project that I’m architecting out with ER Studio the table designer is one area where I do often cheat and still use the GUI.  Adding columns, data types, setting options, primary key’s etc.  It’s just a lot quicker for me with the designer.

Modifying an existing table however introduces some areas where you should really be careful.  It’s easy to cause an operation that may run for hours, days, or until the end of your career (which could be of variable length depending on the operation you just kicked off).  You need to know your options when modifying existing tables.  You also need to know how to script it out – even if you made the changes in the designer.

Table Designer Options

tableopt

The table designer has a few important options.  The one of most interest to many is ‘Prevent saving changes that require table re-creation’.  This is checked by default on new SSMS installs.  Basically this keeps you from accidentally issuing a statement in the table designer that will cause SSMS to have to drop and recreate the table.  Depending on the table size and the data within it – this could be a major process.  I like that this is checked by default.  With this checked – any attempt to implement a change that will require the table to be dropped will present you with this message instead.
saving changes not permitted.  changes you have made require the following tables to be dropped and re-created

There are two steps to get around this.  One you can turn this safety feature off by clearing the option and then clicking save.  If you do this however you better know exactly what you are doing – because you are now working without a net.

So, instead of saving as step two – a much better way around this error is to script out the change instead.  A lot of people don’t know you have this option even if you used the designer to make your changes.  Simply right click the designer window and select the ‘Generate Change Script…’ option.  All your changes will then be scripted out so you can verify everything before committing your change.

genscript

 

I would highly recommend that all changes, even those that don’t require the table be re-created be implemented this way.  Among other benefits the scripts are now auditable, can be source controlled, or can be saved for re-use after you’ve tested your changes in a dev environment and are ready to proceed in production.

If you look back at your options under the table designer section you’ll notice another one that states: ‘Auto-generate change scripts’.  This will in fact generate the change script for you every time you click save.  The problem is – this is in addition to saving it.  I find this feature less useful than it could be.  Especially since it won’t save if the ‘prevent saving …’ option is checked.  If I had my druthers I would have the behavior be to generate a change script INSTEAD of saving the change on the current instance… not in addition to.  In fact, I opened a connect feature requesting just that.

Scripting Out The Effects of Other Designers

Using scripts for actions you’d like to take in a production environment versus clicking your way through a GUI is generally always good practice.  For the most part SSMS does a good job of giving you this option even if you use the GUI to identify the change you’d like to make.  Almost any action in SSMS that implements a ddl change against your database will provide an option to script that action.  Look in the upper left hand corner of the final step of the GUI for the script button.

script

The image above is the GUI that pops up when you right click an object in the object explorer and select ‘Delete’. You’ll also see this option under security objects, mirroring, almost all of the property windows that allow you to change object properties… pretty much everywhere.

You want to move from JV to Varsity? Always implement your ddl changes via script – even if you use the designer to generate that script.

2 comments on “SSMS Day 11: Working With Designers

  1. Vuk
    September 1, 2016

    Great blog!
    I have learned some cool stuff here 🙂

    Aaron Bertrand has an opposite opinion, which should be considered especially when you think about bugs
    http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/14/bad-habits-to-kick-using-the-visual-designers.aspx

    • Russ Thomas ( @SQLJudo )
      September 1, 2016

      Good, glad your picking up some things. As for the article from Aaron Bertrand, I would say we agree in nearly every aspect. I too prefer not to use the designers for effecting any kind of change on an existing object. I only cheat sometimes when building a new table. As for the rest of this post, it’s pretty decidedly, like Aaron, in favor of scripting out your DDL.

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

%d bloggers like this: