How to debug your SQL Server Queries ?

Have you ever wondered why your query is not doing as what you expected?
Have you ever add extra lines in your query to print parameter values, so you better understand what is happening?
If you answer one of these questions with No, you are a really good query builder or you never build queries. If you answer one of these questions with Yes, this blog post can be something for you.
Within SQL 2008 a debug option is introduced in the SQL Server Management Studio (SSMS).

It is the green arrow. In previous versions of SSMS, this green button is the execute button.
To demonstrate the debug option I will use a simple query:

DECLARE @Rownumber INT = 0

WHILE @Rownumber < 10
BEGIN
SELECT @@SERVERNAME
END

This query will never end. This is because @Rownumber does not change.
Now we will start debugging this query.

1. Set your cursor on SELECT @@SERVERNAME and
2. Pres F9 to set a breakpoint. A red circle will appear.
3. Press ALT F5.  In your 'locals' window the @Rownumber will appear without any value.
4. Press ALT F5. Debugging is started and will stop on the breakpoint. The @Rownumber has now a value of 0. This is what you expect.

5. Press ALT F5 to continue. The servername will be returned.
6. Press ALT F5 to continue. Another time the servername will be returned. However the value of @Rownumber is still on zero. This explains why the query never stops. The @Rownumber will never get 10.
7. Stop debugging with SHIFT F5
8. Add next statement SET @Rownumber = @Rownumber + 1
   So we get next script:
   
   DECLARE @Rownumber INT = 0
   WHILE @Rownumber < 10
   BEGIN
   SELECT @@SERVERNAME
   SET @Rownumber = @Rownumber + 1
   END
9. Set breakpoint on the same line: SELECT @@SERVERNAME
10. Start debugging by pressing ALT F5 a few times. You will see that the value of @Rownumber is growing and the query will stop when the value reaches 9.

Enjoy this feature when you do not understand what your query is doing. Unfortunately, this debug feature is only available in SQL Server 2008. Maybe another reason to upgrade to SQL Server 2008. 


If you press [ALT]+[F5], you would complete the debugging session for this demo.
Here are the keystrokes for the debugger with the Standard keyboard setting.

Action	Standard
Start or continue debugging	ALT+F5
Stop debugging	SHIFT+F5
Step into	F11
Step over	F10
Step out	SHIFT+F11
Implement the Run To Cursor command	CTRL+F10
Display the QuickWatch dialog box	CTRL+ALT+Q
Toggle breakpoint	F9
Delete all breakpoints	CTRL+SHIFT+F9
Display the Breakpoints window	CTRL+ALT+B
Break all	CTRL+ALT+BREAK
Display the Watch 1 window	CTRL+ALT+W, 1
Display the Watch 2 window	CTRL+ALT+W, 2
Display the Watch 3 window	CTRL+ALT+W, 3
Display the Watch 4 window	CTRL+ALT+W, 4
Display the Autos window	CTRL+ALT+V, A
Display the Locals window	CTRL+ALT+V, L
Display the Immediate window	CTRL+ALT+I
Display the Call Stack window	CTRL+ALT+C
Display the Threads window	CTRL+ALT+H