Writing new code = bugging. That part’s easy.

Taking those bugs back out, that’s the hard part.

Developers are used to their tools having built-in ways to show what line of code is running now, output the current content of variables, echo back progress messages, etc. For a while, SQL Server Management Studio also had a debugger, but it was taken out of SSMS v18 and newer versions. Even when it was around, though, I wasn’t a big fan: SQL Server would literally stop processing while it stepped through your query. This was disastrous if your query was holding out locks that stopped other peoples’ queries from moving forward – and you just know people were using it in production.

I do wish we had an easy, block-free way of doing T-SQL debugging in production, but T-SQL debugging is different than debugging C# code. So if your T-SQL code isn’t doing what you expect, here are a few better ways to debug it.

Option 1: Use PRINT statements.Since the dawn of time, developers have put in lines like this:

BEGIN TRANPRINT 'Starting access date changes'UPDATE dbo.UsersSET LastAccessDate = GETDATE()WHERE DisplayName = N'Brent Ozar';PRINT 'Done with access date, starting reputation changes'UPDATE dbo.UsersSET Reputation = Reputation / 0WHERE DisplayName = N'jorriss';PRINT 'Done with reputation changes'COMMIT So that when the statement fails, they can at least see which part failed:

There are a few problems with this approach:

• PRINT doesn’t output data immediately. SQL Server caches the data that needs to be pushed out to the Messages. If you’re troubleshooting a long-running process, you probably want to see the messages show up immediately, as soon as they’re executed.
• PRINT pushes data out over the network whether you want it or not, adding to the overhead of your commands. This isn’t a big deal for most shops, but when you start to exceed 1,000 queries per second, you’ll want to shave overhead where you can. You only really want the debugging messages coming out when you need ’em.

Let’s raise our game with RAISERROR.

Option 2: Use RAISERROR, pronounced raise-roar.What? You didn’t notice that it’s misspelled? Okay, confession time, I didn’t realize that either – Greg Low of SQLDownUnder pointed it out to me. Let’s add a little more complexity to our code:

DECLARE @Debug BIT = 1;BEGIN TRANIF @Debug = 1RAISERROR (N'Starting access date changes', 0, 1) WITH NOWAITUPDATE dbo.UsersSET LastAccessDate = GETDATE()WHERE DisplayName = N'Brent Ozar';IF @Debug = 1RAISERROR (N'Done with access date, starting reputation changes', 0, 1) WITH NOWAITUPDATE dbo.UsersSET Reputation = Reputation / 0WHERE DisplayName = N'jorriss';IF @Debug = 1RAISERROR (N'Done with reputation changes', 0, 1) WITH NOWAITCOMMIT I’ve added a @Debug parameter, and my status messages only print out when @Debug = 1. Now, in this example, I don’t really need a parameter – but in your real-world stored procedures and functions, you’re going to want one, and you’ll want the default value set to 0, like this:

CREATE OR ALTER PROC dbo.DoStuff @MyParam VARCHAR, @Debug BIT = 0 AS... That way, you only turn on the debug features manually when you need ’em, but the app doesn’t call @Debug, so it just gets left at its default value, 0.

I’ve also switched to RAISERROR instead of PRINT because RAISERROR has a handy “WITH NOWAIT” parameter that tells SQL Server to push out the status message to the client right freakin’ now rather than waiting for a buffer to fill up.

When you’re troubleshooting long or complex processes, you’re probably going to want to dynamically drive the status message. For example, say it’s a stored procedure that takes hours to run, and you wanna see which parts of it took the longest time to run. You’re not gonna sit there with a stopwatch, and you’re not gonna come back later hoping that the queries will still be in the plan cache. Instead, you wanna add the date/time to the RAISERROR message.

Unfortunately, RAISERROR doesn’t support string concatenation. Instead, you have to pass in a single string that has everything you want, like this:

DECLARE @Now NVARCHAR(50);SET @Now = CONVERT(NVARCHAR(50), GETDATE(), 26);RAISERROR (N'Done with reputation changes at %s', 0, 1, @Now) WITH NOWAIT Which gives you the date at the end of the output:

You can even pass multiple arguments in – check out the RAISERROR syntax for more details on how the arguments work.

Option 3: Use Table Variables.You’ve probably heard advice from me or others warning you that table variables lead to bad performance. That’s true in most cases – although sometimes they’re actually faster, as we discuss in the Fundamentals of TempDB class. However, table variables have a really cool behavior: they ignore transactions.

BEGIN TRANDECLARE @Progress TABLE (StatusDate DATETIME2, StatusMessage NVARCHAR(4000));INSERT INTO @Progress VALUES (GETDATE(), N'A one');INSERT INTO @Progress VALUES (GETDATE(), N'And a two');ROLLBACKSELECT * FROM @Progress ORDER BY StatusDate; So even though I did a rollback, not a commit, I still get the contents of the table variable:

This is useful when you’re:

• Troubleshooting a long-running process
• The process has try/catch, begin/commit type logic where something might fail or roll back
• Desiring the results in tabular format, possibly even with multiple columns, XML, JSON, whatever

And there you have it – 3 ways to work through debugging without using the discontinued SSMS Debugger. I typically use RAISERROR myself – it’s easy enough to implement, and it’s a technique you’ll use forever. There are more ways, too, and you’re welcome to share your favorite way in the comments.