SQL Server 2005 introduced a new
tool for managing servers and
databases: SQL Server Management
Studio (SSMS). SSMS has more features than
its predecessors in earlier SQL Server versions—and
some of those can take a little while to figure out. To
help ease the learning curve, I’ll provide some tips on
how to use SSMS effectively for tasks such as querying
and reporting, focusing on features that are less obvious
to new users. I’ll base this discussion on SSMS in SQL
Server 2005 SP2. If you’re using an earlier version,
some features, captions, or menu options might be
slightly different.
Exploring Object Explorer
When you open SSMS, most often you’ll either use the
Object Explorer window to work with servers and databases,
or you’ll execute queries. Object Explorer features
a welcome enhancement: asynchronous loading
of object lists. Rather than making you wait while a list
of objects is retrieved from the server, SSMS gives you
a visual notification and lets you proceed with other
tasks in the meantime.
You might have noticed that when you right-click
most objects in Object Explorer, you get many options
for scripting the object (e.g., Create, Alter, Drop). You
can output the script into a new window, the Clipboard,
or a file. What you might not know is that you
can drag and drop almost all objects from the Explorer
window into a query window. In most cases, SSMS
outputs the object’s name into the query window. But
for the Columns node, SSMS writes out the names of
all columns in the table, potentially saving you a lot of
typing. The table node offers many other productivity
enhancements, such as improved scripting for INSERT,
UPDATE, DELETE, and many other statements,
as well as the ability to view the data or design the
table. The table node’s Edit function is somewhat misnamed;
the Edit function pretty much just generates a
CREATE script for re-creating the table.
Besides working with databases, SSMS also lets
you connect to and work with SQL Server Reporting
Services (SSRS), SQL Server Integration Services, SQL
Server Analysis Services, and also SQL Server Compact
Edition. This functionality, however, is limited
mainly to administration and management of deployed
packages, reports, or cubes. If you want to build and
debug these types of objects, you have to use Business
Intelligence Development Studio. Despite the fancy
name, it’s really Microsoft Visual Studio 2005 equipped
with SQL Server–related projects.
Working with Queries
One of my preferred options when working with queries
is to have query results open in a full
window. This option was somewhat easier to
find in Query Analyzer than it is in SSMS.
If you want to maximize the screen showing
the results, go to Tools, Options, Query
Results, SQL Server, Results to Grid or Results to Text
and select Display Results in a separate tab. Now the
query opens in a separate subtab, but it’s still grouped
together under the same tab with the Editor and Messages
windows. You can use the F6 keyboard shortcut
to switch between the query and the results.
The query results window has two neat improvements
in the grid mode that aren’t in Query Analyzer.
First, when you highlight cells in the results and paste
them into Microsoft Excel or another application,
SSMS automatically inserts column names in the first
row, saving you the work of typing them explicitly.
Note that this feature isn’t enabled by default. You can
enable it in the SSMS settings; to do so, open Tools,
Options, Query Results, SQL Server, Results to Grid
and set the Include column headers when copying or
saving the results option on.
The other not-so-obvious functionality is that you
can now rearrange the order of columns in the results by simply dragging and dropping the column headers.
This capability is especially useful when you’re working
with the results returned by a stored procedure and
you want to change the order of the columns so you
can examine the data more easily. You can now do so
without rewriting the query in the stored procedure.
In SSMS, Microsoft rolled up the functionality of
SQL Server Enterprise Manager and Query Analyzer
into one tool. The most common argument I’ve heard
from DBAs against SSMS is that it can be overkill if
all you need to do is quickly open a query window and
run some queries. Although I love SSMS—and have
been using it for more than two years—occasionally
I’ll still open Query Analyzer (via a keyboard shortcut
saved on the desktop) when I need to do a querying
task quickly.
If you fall into this camp, I might have good news
for you. At the 2007 Professional Association for SQL
Server (PASS) Community
Summit, I was in a
session given by a member
of the SQL Server tools
team who mentioned that
Microsoft was seriously
looking into providing
the ability to load SSMS
in a lightweight mode
without Object Explorer
and other windows.
This implementation—
if it happens—should
improve the experience
for those who still desire
the speed and simplicity
of Query Analyzer. In the
meantime, though, here are my recommendations for
making SSMS more Query Analyzer–like:
• Close unnecessary windows. SSMS comes with many
windows docked on the right or on the left side—
for example, Properties, Template Explorer, and
Toolbox. You can eliminate some of these distractions
and recover useful screen space by closing the
windows you don’t use often and setting the ones
you use to Auto Hide (by clicking the Auto Hide
pushpin so that it points to the left). I typically keep
only the Registered Servers, Object Explorer, and
Solution Explorer loaded and set to Auto Hide.
• Disable splash screen loading. In my tests, disabling
the splash screen can cut the load time for SSMS in
half. The load time is still hard to predict because
sometimes for no apparent reason SSMS takes 10 to
15 seconds to load, but most of the time it loads in
a second or two when the splash screen is disabled.
You can disable the splash screen by adding the
-nosplash command-line parameter to the SSMS
shortcut, as Figure 1 shows.
• Disable certificate check. Many Microsoft programs
run a check over the Internet to verify the
publisher every time you start them. Disabling this
option can boost the load time. You can disable this
check using Microsoft Internet Explorer; to do so,
go to Tools, Internet Options, click the Advanced
tab, and scroll down to the Security section. Next,
uncheck the Check for publisher’s certificate revocation
option. You should disable the certificate check
only if you notice a difference in load time when
this option is off—and preferably not on production
servers. (For more information about the effectiveness
of disabling the certificate check, see Euan
Garden’s blog post at blogs.msdn.com/euanga/archive/2006/07/11/662053.aspx.)
Continue to page 2
Prev. page
[1]
2
next page 
