SQL Query Analyzer had a neat debug facility for Stored Procedures in SQL 2000. It went missing in SSMS 2005 but at least we could use the Visual Studio debug facility for both .NET and Transact-SQL code (as well as Integration Services packages). Now in SQL 2008 we have the debug facility in SSMS itself. And it’s now for more than just for Stored Procedures.
I have been testing SQL Server 2008 RC0 and the enhancements to SSMS are more than welcome. I have already talked about Intellisense and Multi-Server Queries in previous blog entries. The SSMS debug facility is very easy to use and you can test out any Transact-SQL code modules as long as it runs against a SQL 2008 instance. No backward compatibility here. Of course, you can set breakpoints, view and update variables, step into, step over and step out just as you would expect. The great thing is that if a statement executes a stored procedure, the debug facility enters that code automatically when you use the Step Into option. This is good for walking through nested code. Even if it is a system Stored Procedure. This is a great way to go to school on Microsoft T-SQL coding practices. Also, if a trigger is present on a table, the debug facility will enter that code too and you can watch it execute. You can also have the debug facility running while other script windows are open and jump between them. Very nice. However, you can only have one debug session running at a time. That makes sense, I suppose. We wouldn’t want to be able to confuse ourselves now, would we?
Later
Brian
