#0128 – SQL Server – SSMS – Tutorial – Part 03 – The T-SQL Debugger (L200)


More often than not, there is a myth in the developer community that Microsoft SQL Server does not provide the debugging abilities that other IDEs provide. Even SQL Server 2000 provided basic debugging ability. Starting Microsoft SQL Server 2005, the product now comes with it’s own Management Studio, which is based upon Microsoft’s renowned IDE – the Visual Studio. Therefore, the most common debugging abilities come out of the box for SQL Server – enough to give application developers a run for their money!

The upcoming, SQL Server 2012 takes debugging a step further – bringing conditional breakpoints, data-tips and what have you. Let’s have a look at some of the basic debugging options that are available to us in SSMS.

NOTE: For T-SQL Debugging, connecting the Transact-SQL editor to the server with a login having the sysadmin fixed server role is not sufficient; the Windows login of the Visual Studio user must also be a member of the sysadmin fixed server role on that instance of SQL Server.

Which objects can be debugged?

All commonly used objects within SQL Server can be debugged. These include, but are not limited to:

  • Ad-hoc queries
  • Stored procedures
  • Triggers

The way to debug any of these objects is same as Visual Studio.

  • Start debugging – F10
  • Step into an object – F11

We will keep things simple for this session, and raise the bar as we go along.

Debugging in SQL 2012 – Breakpoints

Test Script for this session

We will debug the following script in this session on SQL 11 (“Denali”) CTP03. It is a very simple script that loops through for 20 iterations.

DECLARE @iCounter INT = 0

WHILE (@iCounter <= 20)
BEGIN
    PRINT 'Hello World! The counter reads: ' + CAST(@iCounter AS VARCHAR(2))

    PRINT 'Hello Nakul! The counter reads: ' + CAST(@iCounter AS VARCHAR(2))

    SET @iCounter += 1
END
GO

Creating Conditional Breakpoints & Labeling them

One of the major features that was missing in the earlier versions of SSMS was the ability to have conditional breakpoints. In SSMS for Denali, conditional break-pointing is possible. To explore it’s features, let’s start debugging by pressing the F10 key as high-lighted in the Hands-On session. I would re-iterate that debugging in SSMS is not at all different than debugging in VS2010.

To demonstrate the fact, let’s place a breakpoint near the line for “Hello World!”. We will make this a conditional breakpoint, to break only if the value of the counter hits 10.

Hit F9 to setup the breakpoint on the desired line

Note that the information in the “Breakpoint” window is non-descriptive. Let’s correct that first

image
Right-click on the breakpoint, and choose “Edit Labels”

image
In the “Edit Labels” window, type in a descriptive detail and click “Add”

If pre-existing, you can choose one if you like

image
On clicking “OK”, notice that the breakpoint can now be identified easily amongst a pile of breakpoints image
Now, right-click on the breakpoint and go to “Condition” image
Add the required expression as a condition and click “OK” image
Notice that in both the “Breakpoints” window &  editor window, the breakpoint symbol now has a little "plus (+) sign in it image
Let’s create a “Hit Count” based breakpoint for the second line image
For the test, let’s set this breakpoint to hit at intervals in multiples of 4 – at 4, 8, 12,16 & 20.

Hit OK once done to set the breakpoint

image
The Breakpoints window would look like the one in the screenshot image

Hit “Alt + F5” to run through the sample code. The first breakpoint hit will be “Hello Nakul!” when the hit count is 4. To verify whether the hit is correct or not, you can either use the “Locals” window or the “Immediate” window (Go to Debug –> Windows –> Immediate or press Ctrl+Alt+I):

image

Hit Alt+F5 again. The next breakpoint to be hit will again be “Hello Nakul!”. Hit Alt + F5 again, and we will hit the “Hello World!” breakpoint and so on.

Searching for specific breakpoints – using Labels to your advantage

Go to Debug->Windows->Breakpoints or press Ctrl+Alt+B to launch the Breakpoints window.

While we only have 2 breakpoints in this session and it is quite easy to remember them, in production code, there might be tens of breakpoints and it might be difficult to remember each one. What’s more, out of all breakpoints, you might want to work with only the breakpoints that are related to a particular operation – an update to a given table, for example.

In the breakpoints window, take the time out to label each breakpoint carefully. You may want to use your labels such that each label identifies a particular group of operations in question – for example, a label can be – “Updating Production”, “Updating Sales Orders” and so on.

Notice the addition of a “Search” box in the Breakpoint window. The “Search” box allows us to search on a specific column or on all visible columns of the breakpoint window.

image

Choosing “All Visible” in the “In Column” drop-down, let’s type in “World” in the “Search” box and hit Enter. Notice that SSMS will take the other breakpoints off the list. Clearing the search results will bring them back.

image

Exporting Breakpoints

Now, let us see how to export breakpoints from one SSMS session to another.

Once the script file has been saved to any location of your choice, click on the “Export” button on the toolbar of the “Breakpoints” window image
In the File->Save dialog, select a location and file name of your choice to export the breakpoints image

Importing Breakpoints

Now that we have exported breakpoints to a file, let’s see how we can import them into another SSMS session.

Launch SSMS and open the query to debug.

Launch the breakpoints window (Debug –> Windows –> Breakpoints, or Ctrl + Alt +B) and click the Import button:

image
In the File->Open dialog box, choose the breakpoints file saved above image
Notice that the breakpoints are imported and applied successfully such that the IDE is now ready for debugging image

Data Tips

Per MSDN, “DataTips are one of the most convenient tools for viewing information about the variables and objects in your program during debugging. When the debugger is in break mode, you can view the value of a variable within the current scope by placing the mouse pointer over the variable in a source window.

I like to think of it as a very light-weight version of the Watch window. When debugging, almost any troubleshooter likes to be in full control – knowing exactly what is going on where. If a variable changes it’s value, it might be important because it may not be warranted and might produce incorrect results. Data Tips help us in being “aware” about our surroundings, and at times, in controlling the flow of execution.

Please note that to the best of my knowledge, Data Tips are available in all the flavours of Visual Studio 2010.

To Display A Data Tip

Start a debug session image_thumb2
However the mouse over the variable to be evaluated, a Data Tip appears image_thumb4

To Move, Pin and Unpin A Data Tip

Click the “pin” icon image_thumb6
The data-tip is now “pinned” image_thumb8
Drag the data tip to the desired location to move it image_thumb10
Hover over the data-pin and click the “pin” icon again to un-pin the data-tip image_thumb12

Adding Comments to a Data Tip

Click the “Expand” arrow on the data-tip image_thumb13
Add required comments in the edit box image_thumb15
Click anywhere on the “Canvas” image_thumb17

To Close all Data Tips

We have not one, but 3 distinct options to clear out the data-tips on a Microsoft SQL Server Query Editor window.

Option #1 – Debug Menu image_thumb19
Option #2 – The Data tip itself image_thumb21
Option #3 – Right-click the “indicator” column image_thumb23

Limitations

As with any enhancement, there are always things that can be improved upon after the fact. The only major limitation that I see with exporting & importing breakpoint is:

  • The name & location of the script file must be same as that when the breakpoints and data-tips are exported
    • Therefore, if when the breakpoints were exported, the script file was at “E:ScriptsBreakpoints.sql”, I cannot change it to be “E:DevelopmentBreakpoints.sql” for the import

Practice Exercises/How To…?

It’s time to practice! The practice exercises for the T-SQL debugger need some setup time, but are simple and easy.

Create the following stored procedure against the AdventureWorks2008 database:

--Step 01: Create the following Stored Procedure
USE AdventureWorks2008;
GO
IF OBJECT_ID ( 'Sales.proc_UpdateSalesOrderDetailUnitPrice', 'P' ) IS NOT NULL 
    DROP PROCEDURE [Sales].[proc_UpdateSalesOrderDetailUnitPrice];
GO

CREATE PROCEDURE [Sales].[proc_UpdateSalesOrderDetailUnitPrice]
    @tSalesOrderId       INT,
    @tSalesOrderDetailId INT,
    @tUnitPrice          MONEY
AS
BEGIN
    --Wrap the UPDATE in a transaction so that we do not make permanent changes to the DB
    BEGIN TRANSACTION MyDebuggingTest
        UPDATE [Sales].[SalesOrderDetail]
        SET UnitPrice = @tUnitPrice
        WHERE SalesOrderID = @tSalesOrderId AND
              SalesOrderDetailID = @tSalesOrderDetailId
    ROLLBACK TRANSACTION MyDebuggingTest
END
GO
  1. Debug the trigger – Sales.iduSalesOrderDetail by executing the following stored procedure:
        
    USE AdventureWorks2008
    GO
    
    DECLARE @tSalesOrderId INT = 43659,
            @tSalesOrderDetailId INT = 2,
            @tUnitPrice MONEY = 2010.255
    
    EXEC [Sales].[proc_UpdateSalesOrderDetailUnitPrice] @tSalesOrderId, @tSalesOrderDetailId, @tUnitPrice
    
  2. Define a breakpoint within the trigger. Notice that the breakpoint window displays a long number instead of the trigger/object/script name. What is this number?
    1. Hint: Read the article on the Debugger hands-on session in the “further reading” section below

Further Reading

Until we meet next time,

Be courteous. Drive responsibly.

Tweet to @sqltwins

Advertisements

One thought on “#0128 – SQL Server – SSMS – Tutorial – Part 03 – The T-SQL Debugger (L200)

  1. Pingback: #0382 – SQL Server – SSMS – Debugging a T-SQL DML Trigger | SQLTwins by Nakul Vachhrajani

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s