SQL Server 2012 TSQL Debugging enhancements

By:   |   Updated: 2011-07-01   |   Comments (4)   |   Related: 1 | 2 | More > Debugging

Problem

In one of my previous tips (SQL Server 2008 T-SQL Debugger is back in SSMS) I talked how you can debug your T-SQL routine/module. Although it was very handy for SQL Server Developers to debug code, the tools still lacked some of the basic features that are already available for .NET Developers. For example, you are not allowed to add conditions to the breakpoint, filter the breakpoint, getting more information about runtime values or expressions. SQL Server 2012 overcomes these shortcomings with improved debugging in SQL Server Management Studio. So how does SQL Server 2012 save developers from hours of frustrating debugging and helps to improve their productivity? Check out this tip for these answers and more.

Solution

In my previous tip I talked about basic T-SQL debugging (debugging is a mechanism which allows you to find errors in your code by investigating the run time behavior of the code) in SSMS. In this tip, I am going to take that discussion further and talk about debugging enhancements in SQL Server 2012. Now SQL Server Management Studio (SSMS) is powered by Visual Studio so T-SQL Developers, will have almost all of the debugging features available for .NET Developers. SSMS debugging capabilities allow you to debug script blocks, stored procedures, functions and triggers.

To start debugging your T-SQL code, you can either:

  • Click on the Debug button on the Query toolbar
  • Click on the Start Debugging menu item on the Debug menu as shown below
  • Press ALT+F5
debugging your t-sql code

If you are not running SSMS as an administrator (or in elevated mode) and try to start debugging, you will see an exception as shown below. Please note, as I said before SSMS is now powered by Visual Studio.

ssms is powered by visuel studio

Now for a demonstration, let me setup the environment. What I am doing in the code below is declaring a variable and setting its value to 1. Next I have a loop which checks the value of this variable and iterates as long as it is less than equal to 100. Inside the loop I am incrementing the value of variable by 1 on each iteration. Reference the code below:

setting up the enviroment

To set/toggle a breakpoint on a line, go to that line and press F9 or go to Debug menu and click on Toggle Breakpoint menu item. Once a breakpoint is placed you will notice a red circle on the left side of the line as shown below:

go to the deg menu

If you are using SQL Serve 2008 or SQL Server 2008 R2 SSMS and right click on the breakpoint symbol (red circle) you will notice you will have just two options, first to delete the breakpoint or second one to disable the breakpoint as shown below:

using sql server 2008 or sql server 2008 r2

If you are using SQL Serve 2012 SSMS and right click on the breakpoint symbol (red circle) you will notice additional options as shown below. Clicking on the Condition menu item lets you define the condition on which execution will pause on that breakpoint or line. This comes in handy when you have to loop through hundreds or thousands of iterations and you want to pause the execution on the occurrence on certain condition. For example, an expression evaluates to true or the value of variable changed.

using sql server denali

While defining the breakpoint condition, you can specify when the breakpoint will be hit; it could be either when the expression returns true after evaluation or when the value of a variable is changed. After placing a condition on the breakpoint, a white plus sign appears inside the breakpoint red circle to indicate it has condition attached.

defining the breakpoint condition

For example, I have specified the condition that when variable value reaches to 5 breakpoint (execution should pause as shown above) should be hit and this is what has happened as you can see below:

specify the condition that when the variable value reaches the breakpoint

A breakpoint hit count allows you to specify the condition to pause the execution if the breakpoint has been hit for specified number of times. Again this comes in handy when you have a loop through hundreds or thousands of iterations and you want to pause the execution on the certain numbers of iterations.

a breakpoint hit allows you to specify the condition to pause

You can specify the hit count as follows:

  • Break always
  • Break when the hit count is equal to (some specified value)
  • Break when the hit count is a multiple of (some specified value)
  • Break when the hit count is greater than or equal to (some specified value)
specify the hit count

As I have specified the execution to pause on reaching the hit count of 10, you can see it has paused the execution when the value of the variable is 10.

it paused the execution when the value of the variable is hit

Along with Watch window, local window etc. SQL Server 2012 debugger has quick info pop up (also called Data tips) when you move your cursor over a T-SQL identifier as shown below. You also have an option to pin this quick info during the during execution of your session and it will appear again next time you start debugging. You also have the option to add comments while debugging.

sql server denali debugger has a quick info pop up

when you move your cursor over a t-sql identifier you have the option to add comments

Notes

  • As I said in my last tip, SQL Server 2008 and later version supported debugging SQL Server 2008 and later only. With SQL Server 2012, you can debug your T-SQL scripts on SQL Server 2005 SP2 and later.
  • SQL Server 2012 debugging allows changing the variable value at runtime in the local window, T-SQL expression evaluation in Watch & Immediate Window, etc.
  • If you want to debug from a computer other than the instance of SQL Server then you need to configure a firewall rule, click here for more details.
  • The sample code, example and UI is based on SQL Server 2012 CTP 1, it might change in the RTM release or later versions.
Next Steps


Last Updated: 2011-07-01


get scripts

next tip button



About the author
MSSQLTips author Arshad Ali Arshad Ali is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Friday, July 11, 2014 - 2:54:24 PM - Adrian Miranda Back To Top

Excellent and helpful post my friend. Greetings from Costa Rica.


Wednesday, May 22, 2013 - 10:46:37 AM - Mike Back To Top

Arshad,

I've typed in the code exactly as you specified in your example, set the Breakpoint condition to @IntCounter = 5, and selected the 'Is true' button; yet when I do this the code executes as if there were no breakpoint, printing out all number 1 through 100.

Any ideas on what might be going on?  I've tried this both locally (on my local SQL 2012 instance) and remotely connecting to a SQL 2012 instance.  Is there another setting / configuration somewhere that may need to be set?  I have sysadmin rights on both instances.

Thanks,

Mike


Friday, July 01, 2011 - 9:19:02 AM - Dave Ballantyne Back To Top

IMO , if you are using the debugger you are doing TSQL wrong.   It only helps if you are doing procedural TSQL and as we all know that is not the most efficent.

It *could* be useful if it would help you see inside contents of a temp table.

The best debugging for me is still a 'select' statement.


Friday, July 01, 2011 - 9:02:06 AM - ALZDBA Back To Top

It's a disgrace they stil need "the debugger" to be sysadmin on the sqlinstance !

They should have turned that down to maximum dbowner.
If an instance e.g. isn't configured to run clr stuff, the dev should request to enable it through the propre chanels, and not be able to just enable the thing needed to just do his stuff.

That situation is just untollerable in these times of hippa, sox, segregation of duties, ...



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools