casinos

SQL Server 2012: Debugger basics January 4th, 2013

Vinod Kumar

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.

1. Setup

For this blog post I am using a very simple T-SQL batch.

DECLARE @debugger INT = 0

WHILE( @debugger < 1000 )
  BEGIN
      IF( @debugger%100 = 0 )
        PRINT @debugger

      SET @debugger += 10
  END 

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.

image

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.

image

3. Step through Debugger

Here are some of the shortcuts we need to be aware:

  1. 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.

image

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:

image

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.

image

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:

image

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:

image

6. Location

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.

image

There is nothing much we have in this screen, hence we will go to the next option.

7. Condition

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:

image

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.

image

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.

image

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.

image

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.

image

In our example we have used to print our variable value (@debugger) when the breakpoint is hit. The output window shows the below output.

image

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.

image

image

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.

image

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.

image

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.

image

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.

image

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:

image

Final Words

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.

Tags: , , , , , , ,

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.


6 Responses to “SQL Server 2012: Debugger basics”

  1. Michael says:

    Thanks Vinod, that was my first sql debugging :)

  2. Samith C Valsalan says:

    Nice post , Really helped me to know debugging ..

    Good bye to in between Print n Select :)

  3. Devi Prasad says:

    I could see, you have put a lots of effort for this blog.. Nice one and unique!

    I was not aware about no 12(Bubble tooltip with debugger) Thanks for teaching me a new functionality. Seems like 11.0 is loaded with lot many features to play around..

    I will definitely loud this post with my friends and colleagues..

Leave a Reply