562 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
How to debug T-SQL queries and stored procedures
Part three of a four-part series of blogs
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.
Breakpoints provide a great way to run a query normally until you reach a given statement, and then debug from this point onwards.
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.
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.
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.
|Parts of this blog|
25 Aytoun Street