Is there a way to debug a SQL Server 2008 query?
Yes. You can use the T-SQL debugger: http://technet.microsoft.com/en-us/library/cc646008.aspx
This is how you execute the request: http://technet.microsoft.com/en-us/library/cc646018.aspx
a source to share
What exactly do you mean by debugging? Are you seeing incorrect data? Are you getting unexpected duplicate lines?
What I usually do is start with a known dataset, usually one or two lines at a time if possible, and comment out all connections and conditions.
Enter each additional element in your query in turn, starting with the unions.
At each step, you must know how many entries you expect.
As soon as you enter something like join, or when a condition that doesn't match your prediction, you know you've found the problem operator.
If it is a stored procedure with variables and such, you can always PRINT
value the variables at different points.
If you only want to execute a specific point in the stored procedure, you can RETURN
stop processing at any time.
If you have temporary tables that need to be destroyed between executions while debugging your procedure, a common trick I use is to create a shortcut like -
cleanup:
then at any point I want to enlist I can goto cleanup
(I know goto is terrible, but it works fine when debugging sprocs)
a source to share
To be honest, I think debugging is pretty much useless. I often don't want to see variables, but rather records that I would insert into the table or update or delete.
I do this when I have a complex sp to debug.
First, I create a test variable. I set it = 1 when I want to test. This will ensure that all iteh transactions are rolled back at the end (don't want to actually modify the database until you are sure that proc does what you want.) By making sure the commit statement requires the test variable to be set to 0.
At the end of the proc, I usually have an if = 1 begin END test and between start and end, I put selected entries for all the things I want to see values. This could include any table variables or temporary tables, records in a specific table after insertion, records that I deleted, or whatever else I feel I need to see. If I test many times, I can comment out the table references that I know are correct, and only focus on the ones I have changed that go around. Now I can see what the effect of my proc is and the changes are rolled back if they were wrong. To actually commit the changes (and not see the intermediate steps), I simply change the value of the test variable.
If I'm using dynamic SQL, I also have a debug variable that, instead of executing dynamic SQl, just prints the results to the screen. I find all of these much more useful when debugging a complex script than breakpoints that show me the meaning of variables.
a source to share