I know all of us have used SQL Server T-SQL code in our projects and application extensively. But there is one thing about SQL Server that all the Visual Studio developers hated – it is in the area of debugging. Yes, we did have debugger in earlier versions too but we hardly used them extensively because SELECT or PRINT were our best friends when it came to debugging SQL Server code. The SQL Server 2012 Management Studio is built on top of the VS shell and now we have some interesting debugging options out-of-box to play with. In this post we will discuss these. Do try to play around with them and don’t forget to pass me your comments if you plan to use them in your Dev/Test box.
For this blog post I am using a very simple T-SQL batch.
DECLARE @debugger INT = 0
WHILE( @debugger < 1000 )
IF( @debugger%100 = 0 )
SET @debugger += 10
All our demonstration in this post will be around this small T-SQL block :).
2. Enable a Breakpoint
There are multiple ways to initialize a breakpoint at a location. In our example we will set a debugger at the SET construct location where we are adding 10 to @debugger. Place your cursor at the location you want the debugger to be placed and use Toolbar.
As you can see from the diagram, we can also use Ctrl+Alt+B as a shortcut if you are a keyboard person.
Though I am also a keyboard person, here I have always felt a much more easier option. On the left side of script, there is a small strip that exists. Just left click at this location and this enables a breakpoint.
3. Step through Debugger
Here are some of the shortcuts we need to be aware:
- Start batch in debug mode –> Alt + F5.
The toolbar for debugger comes up with all the shortcuts we need to know. Here is a snapshot of the same.
I am sure if you are a VS developer this looks quite surprising and most importantly familiar to what you have been doing inside VS all these days :).
4. Start Debugging – Locals
For our above code we have set an debugger at the SET statement. We loop through the debugger using F10 or Alt+F5. An important window to look out is the “Locals” Window at the bottom of the screen. This shows the various variables we are working with. In our example the first iteration shows:
Pressing Alt+F5 and reaching value of 100 we can see in the messages Tab our Print command has fired. We will see 0 and 100 there.
At this point in time we are going to reset the value of the local to 890 in the Locals window. Just double click the value column where it is 100 and make the change and press enter. Pressing Alt+F5 we can see that we have evaluated for 890+10 now and there is 0, 100 and 900 in the messages tab. This is a great way to step to a value and see the effect. Now the output on the messages tab shows:
Shift+F5 stops the debugger. Next we will see what are the other advanced interesting features added.
5. Debugger Options
Right click at the debugger circle and we are presented with an interesting set of options. The screen will look like:
Location gives us an option to save the file with the debugger information and if we are using source-control systems for our .sql files like TFS we can opt to keep this debugger code separate from the original file.
There is nothing much we have in this screen, hence we will go to the next option.
This is where our debugging skills get to the next level. Right click on the debugger circle and select Condition. In this screen we can give a condition when the debugger must be invoked or when the debugger must stop at that location. In our example, we are going to stop in every 90th value is reached. We are doing this because we know the print will happen next. So our condition will look like:
After clicking OK. Let us start the debugger using Alt+F5 and if we continue again using Alt+F5 we can see the debugger doesn’t stop in every 10th value increment but now has jumped to value of 90 as in the diagram below. If we again continue, it will stop at 190, 290, 390 and so on.
8. Hit Count
This is really interesting option. We can set the breakpoint to stop when the number of times the breakpoint hits. In the above case, we used 90. The same can be achieved here too using the Hit Count of 10. The logic here is the breakpoint first get hit for 0 and then the 10th value is 90. So we can give a condition that states, break when the breakpoint is hit every 10th time. That way we can get 90, 190, 290 and so on.
Remove the condition created before and we will right click the debugger bubble and select Hit Count from the menu.
You can see we have multiple options to play with. But for our example we have used the multiple of 10 route. Press OK and then use Alt+F5 to keep looping and breaking at 90, 190, 290 and so on …
9. Breakpoint Filter
In a multi user or multiple application scenario you want to debug when from a specific machine. In this case we can use this filter. I am sure you might look at this from a Remote debugging point of view. But in my example, I have not used this high-level filter.
10. When Hit
This is an interesting option to play with. It is like our previous ways of debugging with SELECT or PRINT given in an interesting format. Right Click on the Debugger bubble and select “When Hit”. Here we can add something to get printed in our Output when the debugger is hit.
In our example we have used to print our variable value (@debugger) when the breakpoint is hit. The output window shows the below output.
If we look at the “When Hit” debug window, it has some special functions that we can use to know the SP that called this code, nesting, thread, process info etc. I am sure you can play around to see which one suits your needs.
11. Breakpoint Label
We also have an option to Label our breakpoints for easy understanding. In our example we have used the label as “Increment Label” and this can be defined by selecting “Edit Labels” on our right click bubble at the breakpoint location.
12. Bubble tooLtip with debugger
This is a beauty of this debugger in SQL Server 2012. I just loved this feature. When you are debugging the code. Just hover over the variable and it will give you the current value being executed.
Now if we click the tooltip, we can now change the value in the tooltip itself. We don’t have a need to goto watch or the Locals window. In this case, I have changed the value from 130 to 90 and pressed Enter.
If this is cool, you haven’t see what else it can do. If you watch closely next to the tooltip there is a “Pin” symbol. Click over the same. Now you can see that the debugger has gone ahead and pinned the tooltip for easy view.
If we loop through the debugger using Alt+F5 we can see the tooltip gets the updated value. If we want to hold a comment while we are debugging, expand the arrow you see at the tooltip and add your comments.
After I close the debugger session we will see a Pin symbol at the left bar. If we hover over it, we will see the comments that we just entered. The screen will look like:
Wow, that is a ton load of images and cool debugger features with SQL Server 2012. I have loved to see these enhancements finally come to T-SQL procedural code developers. Hope you will use them and feel free to pass me your comments.
This entry was posted on Friday, January 4th, 2013 at 08:30 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.