Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

A guide to advanced new features in SQL Server Management Studio 2008, part 2

SQL Server Management Studio 2008 has several new features, including the T-SQL Debugger for easier debugging and a new Object Explorer Details window that's easier to use.

This is the second part of two articles that cover the new features in SQL Server Management Studio (SSMS) 2008 released in August 2008. The first part covers basic features in SSMS 2008.

This article outlines what's new and improved in SSMS, as well as how you can use these new features for everyday tasks.

T-SQL Debugger
If you have done some programming, you know how important it is to have a good debugging tool. Arguably, a large portion of T-SQL code is written for administrative and maintenance tasks, so debugging isn't as crucial as it is for application code. But many programmers still write complex application code in T-SQL, and benefit from a debugger. In the past, Query Analyzer featured a T-SQL debugger, but it posed reliability issues. In fact, many users were surprised to find that SSMS 2005 had no debugger. In the absence of a debugging tool, their only option was to debug in Microsoft Visual Studio. But with SSMS 2008, a debugging tool has finally been integrated into the SQL environment. But SSMS 2008 has a significant limitation: The debugger works only with SQL Server 2008. If you want to debug older versions, you have to install client tools for a prior version.

T-SQL Debugger includes all the core debugging features -- the Locals, Watch, QuickWatch and Output windows, as well as the ability to set breakpoints to stop execution.

More on SQL Server 2008:

 One welcome feature enables you to step into stored procedures that are executed from the code. To use this feature, place a breakpoint or pause at the line that calls the procedure and press F11. You can also step into user-defined functions. Another useful feature is the ability to change values of local variables and parameters.

You start the debugger by clicking on the button with a green arrow. The decision to use this icon, and its placement right next to the Execute button, is controversial. The Execute button in Query Analyzer features the same icon, so users can get confused. I recommend removing this icon from the Standard toolbar to avoid this problem. To remove an icon from a toolbar in SSMS, click on the down arrow on the right side of the toolbar and select Add or Remove Buttons. Then, when you need to debug, add the Debug toolbar to the menu. As a result, the Debug button won't appear right next to the Execute button. Plus, the Debug toolbar contains additional icons for debugging.

If you work with a local instance of SQL Server, debugging should be easy. Debugging a remote server, however, is more complicated. You may need to configure Windows Firewall on both machines (for more details, consult SQL Server Books Online). In my experience, debugging a remote instance often fails. Rather than troubleshooting remote errors this way, it is almost always easier to log on to the remote server through Remote Desktop and debug locally. Finally, avoid debugging on production servers at all costs, because debugging can lock objects for a longer period than usual, especially when using workload-intensive transactions.

Enhanced Object Explorer Details window
The Object Explorer Details window now features several useful enhancements. First, by default, the window now includes more columns than the previous version. The columns that display by default also contain more detailed information. Furthermore, you can right-click the column headings and select additional columns to display. This window is dynamic: The display changes as you select different objects in the Object Explorer window. If you have not worked with this window yet, hit F7 as you browse database objects in the Object Explorer window. The Details window should open, providing additional information about the object currently selected in the Explorer window. As you move to other objects, this window automatically refreshes. SSMS saves selected columns for each object type, so when you return to viewing the same type of object (i.e., a database, a table, etc.), it will display the same level of detail. The following snapshot shows the Object Explorer Details window displaying info about the tables in the AdventureWorks database. I selected additional columns to display, such as data space used, index space used and the row count for each table:

Click on image for larger version

Another significant new feature in the Object Explorer Details window is object search. The toolbar now allows you to type in the name of an object and search for it. The search is context sensitive: It searches objects you select in the Explorer window. If, for example, you want to search for a table within a single database, you select the database in the Explorer window. If you want to search in all databases, select the Databases node. Unfortunately, in a search, the only way to select more than one database is to select all of them. The search displays the list of objects found, including the path to each object. You can jump directly to the object from the search by right-clicking the object and selecting Synchronize. SSMS will navigate down to the object while expanding and selecting the parent nodes in the Explorer window. Finally, the Object Explorer Details window now includes Back and Forward buttons, allowing easier navigation within a set of database objects.


Roman Rehak is a senior database architect at MyWebLink.com in Colchester, Vt. He specializes in SQL Server development, database performance tuning, ADO.NET and writing database tools. Roman also serves as the technical chair for the SQL Server track at the annual DevTeach conferences in Canada and is the president of the Vermont SQL Server User Group.


Do you have a comment on this tip? Let us know.

Dig Deeper on SQL Server Migration Strategies and Planning

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.