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