I'm curious if anyone else has figured out a good system for performing analysis on a query that may have multiple CTEs stacked. While some problems can be resolved by considering the T-SQL and making logical evaluations, sometime it's nice to be able to see the intermediate data so you can see what is really going on under the hood.

When I have such query, I have the following steps:

1) add a /* and */ delimiter on the bottom part of the query to comment out all subsequent steps2) comment out the line after the closing parenthesis of last CTE to make the next CTE a outer statement3) run the query, analyze the data4) uncomment the comment from #2, move the /* to the next CTE and comment out the next line after the new closing parenthesis. Repeat until we reach the final statement.

This works but I wondered if there was a more effective and a bit less time-consuming & error-prone approach that would permit for data analysis of individual steps.

Intermediate results sometimes can be calculated separately and stored to temp tables. Post your cte if you want more specific answer. There is no debugging tool that i am aware of, that will show you how sql internally executes the command (cte is executed as a SINGLE command) and show you the intermediate data. Execution plan is closest to it.

Banana-823045 (3/20/2013)I'm curious if anyone else has figured out a good system for performing analysis on a query that may have multiple CTEs stacked. While some problems can be resolved by considering the T-SQL and making logical evaluations, sometime it's nice to be able to see the intermediate data so you can see what is really going on under the hood.

That would require that CTEs be a fundamentally different thing than what they are. The optimizer is handed a CTE by you, the query writer, but that is just a description of the data you want in the form of a question, it is not an actual set of native instructions the database engine can use to retrieve the data. The optimizer will take your query and change it into a native set of instructions. We can get a view into these instructions by looking at the execution plan. In SSMS highlight your query and press Ctrl+L to see one.

Here is an example. Consider this simple query to show all columns in a database:

The queries look very different, however they will deliver the same results. Further, when we look at the execution plans of each we see that the optimizer actually decided to execute them in the exact same way.

So, as you can see, it's not as if the database engine processes your query in steps where it materializes the first CTE, then takes that intermediate result and uses it to materialize the second CTE, and so on. It reworks your query, sometimes many thousands of different ways, looking for an efficient way to execute your query.

Regarding debugging, what you have showed in terms of steps is how you would debug a query with cascading CTEs. If you want a debugging experience where you can see the intermediate results consider using #temp tables.

__________________________________________________________________________________________________There are no special teachers of virtue, because virtue is taught by the whole community. --Plato