This is a part of my ongoing series on the "Underappreciated Features of Microsoft SQL Server", inspired from Andy Warren’s editorial on SQL Server Central.com. You can read the editorial and follow the discussion here.

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. 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! Let’s have a look at some of the basic debugging options that are available to us in SSMS.

Please note that throughout this example, we will be using Microsoft SQL Server 2008, and the AdventureWorks2008 sample database as our reference. Please allow for a larger loading period of this post because the Debugger is best explained visually.

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.

Creating the environment

We can debug almost everything in SQL Server – ad-hoc SQL statement batches, stored procedures and even triggers! To begin with, we will create the following stored procedure on the SQL Server 2008 instance. The purpose of using a stored procedure that updates the [Sales].[SalesOrderDetail] table is that the table has a trigger on it and hence it would be easy for us to see how to debug triggers in Microsoft SQL Server 2008.

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

Here’s the query that we will be using to invoke the stored procedure in our debugging session.

USE AdventureWorks2008
GO

DECLARE @tSalesOrderId INT = 43659,
        @tSalesOrderDetailId INT = 2,
        @tUnitPrice MONEY = 2010.255

EXEC [Sales].[proc_UpdateSalesOrderDetailUnitPrice] @tSalesOrderId, @tSalesOrderDetailId, @tUnitPrice;

Performing the Debug session

We can initiate the Debugging session by clicking the “green arrow” button on the SSMS toolbar or by going to Debug –> Start Debugging or by using the Alt+F5 key combination.

Note that SQL Server shows reminds you that a Debug Session is in progress by showing the “Debugging Query…” message in the query status bar. You also get the Local Variable watcher and the Call Stack/Breakpoint editor and Output Windows.

Initiate Debugging session (Use F10 to advance to the next statement)
Use F11 to “step-into” the stored procedure Notice that both “Locals” and “Call stack” windows are updated
Use F11 on the UPDATE statement to step into the Trigger! Also notice that the “Call Stack” shows both the Server Instance name and the Database name where the object being debugged resides
Once inside a trigger, define a breakpoint by pressing F9, use “Continue” (Alt+F5) to proceed with the execution until the breakpoint is hit Is it just me or the breakpoint window is not what we expected to be?
You also get the standard debugging options like “Run to cursor”/”Set next statement”, etc

What is not supported?

The SQL Server Debugger is powerful, but still in it’s infancy. To the best of my knowledge, the following cannot be done in SQL Server 2008 debugger:

1. Edit & Continue – the objects that you step into are temporary scripts created by SQL Server – making changes to these will not have any effect
2. Conditional Breakpoints – its a take it or leave it situation with breakpoints and SQL Server – you cannot have conditional breakpoints
3. Pre-set breakpoints – Because SQL Server opens up a temporary copy of the object scripts, one cannot pre-set breakpoints before the object is “stepped into”

Interesting information in the breakpoint window

Talking about the limitations of Transact-SQL Debugging, did you notice anything strange in the screenshot where we hit the breakpoint (the second-last screenshot in the table above)?

You had to go back and take another look, didn’t you? No worries – I had to do that too!

The content in the breakpoint window is not what we expected it to be. Instead of showing the object name where the breakpoint is, SSMS is showing us a number. What can this number be? It’s the object_id of the trigger we are currently in! Here’s the confirmation:

SELECT * FROM sys.objects WHERE object_id = 1563152614

Result:

 

But why does SSMS give us the object_id instead of the object name? That’s because it is SQL Server’s way of keeping track of the breakpoint for future debugging sessions within the solution/project. If you are not running a debug session under a solution/project, closing SSMS will reset all breakpoints.

But don’t take my word for it. Rerun this debug session and see for yourself the importance of using the object_id. Once done, close SSMS. Reopen SSMS and run through this debug session once again – the breakpoint will be gone!

Important Resources

Here are some important MSDN resources to get you started on T-SQL Debugging

• Setting up Transact-SQL Debugging – http://msdn.microsoft.com/en-us/library/s4sszxst.aspx
• Transact-SQL Debugging Setup Errors and Troubleshooting – http://msdn.microsoft.com/en-us/library/s7ahaxtd.aspx

Powered with these resources, I hope that you will be able to unleash the power of T-SQL Debugging and use it for your making a smarter workplace. If you find any cool tricks to use with SQL Debugging, please share them here!

Until we meet next time,

Be courteous. Drive responsibly.