Digging into SSMS

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:

SSMS can also execute queries in SQLCMD mode. SSMS executes these queries by using the Sqlcmd utility, letting you include Sqlcmd extended commands not available in regular T-SQL. You can toggle this mode on and off on the toolbar or from the menu by selecting Query, SQLCMD Mode. This setting is unique for each query window, and the Query menu only shows when the focus in SSMS is on a query window. The query window highlights rows containing SQLCMD commands, making it easier to remember that you’re working in this mode.

Working with Projects

One of the biggest advantages of SSMS’s use of the Visual Studio (VS) paradigm for solutions is that it enables you to organize and use your script files into solutions and projects. Although SQL Server users who have used VS should easily understand the concept of projects and solutions, those coming from mainly a DBA background can find it a little confusing or even not very useful at first sight.

So what is an SSMS project? It’s a collection of connections, script files (with the .sql extension), and other files. A project’s main purpose is to organize and group related files to make working on a particular task or project easier. A solution is a collection of related projects, grouped together for ease of management. If needed, you can include the same project in multiple solutions. You can define one or more database connections in a project. Each script file is associated with the connection it was created with (although you can change the connection on the fly if you need to execute the script against a different database).

As a consultant working on multiple projects for multiple customers, I’ve come to greatly appreciate the ability to group and organize project files as well as my collection of saved scripts that I use for almost any project I’m working on. It’s also much easier to check files into and out of SourceSafe when they’re grouped in an SSMS project. Once you understand projects and solutions, you’ll see the logic of organizing your existing scripts into related projects to make it easier to find them and work with them to improve your own productivity.

When you work with projects, one of the frustrating and least intuitive tasks is changing the default connection for a project script file to point to another database. When you look at the file’s properties, the Associated Connection property is grayed out; SSMS doesn’t let you change it. Interestingly, if the file has no default connection, SSMS lets you type in the connection name—and only then “locks” the setting. If you change the connection in the query window, the file resumes the old connection when you reopen the file. So at present, it appears that the only way to permanently change the connection associated with a script file is to remove the existing connection from the project.

SSMS has many improvements over its predecessors, but in my everyday work two features stand out for me. The first is object filtering: You can now rightclick almost any list of objects in Object Explorer and filter by name, schema, and creation date. I find this simple feature to be a huge time-saver when I work with databases that have hundreds of tables or stored procedures. The other feature I find very valuable is the improved dialog boxes. Not only do they open in a modal window, letting you switch back to SSMS if you need to (or open several dialog boxes at once), but they let you capture the generated script instead of executing it. This capability can be useful when you want to save the script for repeated execution as a scheduled job or a stored procedure—as Figure 2 shows for the BACKUP DATABASE command—or when you want to examine what SSMS executes behind the scenes.

Unfortunately SSMS still doesn’t provide Intelli- Sense to save you from typing the object names in full. The buzz is that SSMS in SQL Server 2008 will provide IntelliSense for SELECT statements. Until then, you can check out SQL Prompt, a third-party code-completion tool from Red Gate Software (www.red-gate.com). SQL Prompt works as an add-on to SSMS, providing IntelliSense and a few other timesaving features, such as intelligent SQL code insertion. I’ve been using SQL Prompt for a while and have found that it saves me a lot of time typing in SSMS.

Working with Reports

SSMS includes a set of built-in reports to give you a better view of what’s going on in the database or on the server. These reports are implemented via SSRS technology. You can open a report by right-clicking an object in Object Explorer and selecting Standard Reports. A new feature of SSMS in SQL Server 2005 SP2 is the ability to include your own custom reports and have them easily accessible, like any built-in report. Rolling out your own reports for SSMS is fairly easy if you’re somewhat familiar with building reports using SSRS. Just create a report using SSRS, copy the Report Definition Language (RDL) file to any folder, and open the report by right-clicking the SQL Server instance node in Object Explorer and selecting Reports, Custom Reports from the Object Explorer pop-up menu.

After you’ve opened a report, SSMS adds it to the list of recently opened reports. SSMS passes in a few parameters—such as ObjectName, Object- TypeName, Filtered, ServerName, FontName, and DatabaseName—from the selected node in Object Explorer. Your report can use these parameters, so that you can develop context-sensitive reports that are well integrated into SSMS. I’ve included a custom report called Service Broker Queues with this article, which you can download here. The UI for Service Broker in SSMS is limited, so this report will make it easier for you to see the queues in the database, their status, and the number of messages in each queue. Note that the report runs in the database context and needs to receive the Database- Name parameter from SSMS. So, to execute the report, you need to select a database node (or any subnode below the database node) in Object Explorer.

Tap into SSMS’s Power

SSMS in SQL Server 2005 is packed with functionality to help you more effectively manage queries, organize resources relevant to a management task, and work with reports. Explore SSMS for yourself, and let me know if you find any more “hidden” features.