One of the great new features of SSMS 2008 is the new debug facility. Query Analyzer had a stored procedure debug facility in SQL Server 2000 but it was not transferred when SQL Server 2005 combined Enterprise Manager and Query Analyzer. This was one feature that slipped through the cracks and went missing. We had to switch to Visual Studio and debug our stored procedures there. But Microsoft has rectified this missing feature with SSMS 2008 so we don’t have to switch to Visual Studio in order to debug our Transact-SQL code.
An easy way to test the new debugging feature is by testing a system stored procedure like sp_helpdb. In a New Query window, enter the statement:
EXEC sp_helpdb
Then enter the debug facility by clicking Debug/Start Debugging or the green arrow button. The Debug session starts and immediately stops just before the statement executes showing a yellow arrow indicator. At this point, you can step through the code of the stored procedure by clicking Debug/Step Into or F11. You are then shown the yellow arrow again, this time against the first statement inside the stored procedure. The Debug facility is waiting for further instruction. Local variables can be viewed under the Locals tab and a Watch List can be set up by right-clicking a variable and selecting “Add watch”. In order to step through the code line by line, you can use the F11 key again and again. The Watch list will show you the selected variables and their current values. You can change the values if you wish to test out specific scenarios that you don’t have test data for by right-clicking and choosing “Edit Value”.
Of course, you can set breakpoints so you don’t have to stop at every line of code using Debug/Toggle Breakpoint or F9. In this case, clicking Debug/Continue or Alt+F5 will advance to the next breakpoint. If there are no more breakpoints, the code completes and the debug sessions stops with the results of the code displayed.
Debugging code in this way can be very educational, especially if the code is not yours. For instance, in this particular stored procedure sp_helpdb, you encounter a piece of dynamic sql with a comment preceding it. It says “8 KB pages is 128 per MB. If we ever change page size, this will be variable by DB or file or filegroup in some manner; unforeseeable now so just hard code it”. The code is calculating the database size (db_size) using the number of data pages. The page size is currently fixed at 8KB but it is interesting to see what Microsoft is considering. Oracle apparently supports variable page sizes but SQL Server has always been fixed.
Since Microsoft lets us see the code of the system stored procedures and now we can interactively step through that code, I recommend “going to school” on Microsoft developers’ code. You can learn a lot about Transact-SQL and most of the code is well structured and efficient.
And don’t forget to read the comments!
Cheers
Brian
Recent Posts:
Inherit Permissions in SQL Server
Get rid of those NOLOCK hints…
Brian D. Egler, MCITP/MCSE/MCT 2009, is currently an instructor with Global Knowledge, teaching various Microsoft training courses. He is a SQL specialist with a focus on SQL Server, Windows, .Net and XML. Egler has been a technical instructor for over 20 years and has more than 10 years experience with SQL Server, data modeling, database design, application development including IMS, DB2, Sybase. Every year he runs the Boston Marathon for cancer research.
The Leader in Network Knowledge
Yeah - moving this out of
Yeah - moving this out of SSMS 2005 was a dumb move.
My only beef with it's implementation in SSMS 2008 is that the icon is too easy to confuse with F5/Execute. And while I mostly use F5 out of habit to execute a query, every once in a while I use the mouse - and when I do I've been dumb enough to start debugging instead of merely execute (probably due to the fact that Visual Studio has trained me to press the green arrow to 'execute' or start stuff up). And when I do that, it's a bit more painful than I'd like....
But otherwise, I totally agree that debugging is a huge benefit, rightly belongs in SSMS, and that it's an AWESOME way to learn code. (i.e. great post.)
Post new comment