Using breakpoints

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.

Disabling breakpoints

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.