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.