T-SQL Debugger is back in SQL Server 2008 ... debugging now made easy

Let's discuss about debugging a routine like procedure, UDF and Trigger using SQL Server 2008.

Earlier Query Analyzer and Enterprise Manager were separate in SQL Server 2000 then Query Analyzer and Enterprise Manager are merged into 1 tool (SSMS) SQL Server Management Studio in 2005 but without any debugging option. To debug a routine we had to use Visual studio with debugging abilities.

SQL Server 2008 introduced debugging capability that allow to debug routines like procedure, UDF and Triggers. With SQL 2008, we can navigate our code, line by line, watch the state/values of variables, an output window is available to observe what's happening, error windows. We can also move through lines, view call stacks for execution flow, place "breakpoints" to ensure that execution stops at that point to review the state.

How to start debuging

We can start the debugger by either clicking the Debug button on the Query toolbar or by clicking Start Debugging on the Debug menu or pressing ALT+F5 on SSMS.

The image shows a simple statement with breakpoints. It also has a Locals, Breakpoints & Watch window. These are very useful when we are doing complicated calculations.

An example to demonstrate Call Stack window.

create proc prc

as
begin
select GETDATE()
exec proc1
end

create proc proc1
as
begin
print getdate()
end

exec prc

In the above script we have created 2 procedures, in this 1 proc is called from another proc. It has a Call Stack window that shows the execution flow.

SQL Server Management Studio must be running under a Windows/SQL Server account that is a member of the sysadmin fixed server role. If you are running the T-SQL debugger when SQL Server Management Studio is running on a different computer from the instance of the Database Engine, you must enable program and port exceptions by using the Windows Firewall Control Panel application on both computers.

Its recommended that T-SQL debugging should be done on a development/test server only and not on a production server. We can't debug a SQL 2005 database routine from SQL 2008, it throws exception when we try to debug a SQL 2005 routine.

Happy T-SQL coding and debugging. :)