BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
This blog gives the low-down on how to start and use the debugger included in SQL Server Management Studio 2008 R2 and SSMS 2012.
- Debugging SQL stored procedures and queries
- Basic debugging of SQL
- Using breakpoints (this blog)
- Other debugging tools
Posted by Andy Brown on 06 June 2013
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Breakpoints provide a great way to run a query normally until you reach a given statement, and then debug from this point onwards.
Setting and Removing Breakpoints
You can set any statement to be a breakpoint by clicking in the left (grey) margin:
Click in the grey margin to set a breakpoint for any statement (a red circle will appear).
Breakpoints for long SQL statements can be a bit misleading:
It looks like we've set a breakpoint for part of this statement, but in fact this isn't possible!
To remove a breakpoint, just click again (you can also press F9 to toggle breakpoints on and off). When you run a query it will stop at any breakpoint:
|Continue debugging ...||... and execution stops here.|
Note that breakpoints only work when you're already debugging a query (if you execute a query normally, breakpoints will be ignored).
If you disable a breakpoint, it makes it easier to reactivate it in the future:
|Right-click on the red circle ...||... to disable a breakpoint.|
A disabled breakpoint shows up as an empty circle: you can reinstate a disabled breakpoint by right-clicking again.
Listing and managing breakpoints using the breakpoints window
I've never used this window in my SQL career, but I'm feeling in a do-the-thing-properly mood. You can display the breakpoints window as follows:
Choose this menu option to display the breakpoints window.
The window displays more information than you could ever want!
Here we have two breakpoints, one of which is currently disabled. For SQL the Condition and Hit Count columns will always contain the same values. You can click on the Columns dropdown to fine-tune which columns of information are displayed.
Removing all of your breakpoints
Breakpoint build up, and it's a pain having to remove each one individually, so I often use this menu option:
Choose this menu option to remove all of your breakpoints in one go.
For the final part of this blog I'll take a look at some of the other debugging tools available for SQL Server Management Studio, including how to monitor the values of variables that you've created using the Locals window.