How to debug your SQL server query?

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.