Common Table Expressions (CTE)

If you are familiar with the WITH clause in Oracle,
e.g. http://www.dba-oracle.com/t_sql99_with_clause.htm

You might like to check out Common Table Expressions (CTE), which are named, temporary result sets, which can be referenced in subsequent queries.

"A CTE can be used to:

Create a recursive query. For more information, see Recursive Queries Using Common Table Expressions.

Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.

Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.

Reference the resulting table multiple times in the same statement."

Also, check out the following links which provide good examples on how to make use of CTEs.

http://msdn2.microsoft.com/en-us/
library/ms190766.aspx

http://msdn2.microsoft.com/en-us/library/
ms186243(SQL.90).aspx

http://www.databasejournal.com/features/
mssql/article.php/3502676