Common Table Expressions was just what I was looking for where I was needed to do some nested querying. No derived tables required, no table variables required, and no mess. CTE’s are more volatile, and last only as long as the query does. They can be used for DML commands and in views and SPs. An example of a CTE is:
select name,log_reuse_wait_desc from sys.databases
select * from ctetest
The above query works more like a view, but I did not have to create anything previously; any data structure created is also destroyed at the end of the query. You can create more complex queries and even nest them. I am working on a query on finding physical db and logical db info in one query. CTEs might give me the perfect scratchpad to store DMV info which can be joined with other queries to get the desired info.