The Art of SQL Server Database Administration, Development, and Career Skills for the Technically Minded
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.
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.
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
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.
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.
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.
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.