70-433 SQL Server - Common Table Expressions

Firstly looking on wikipedia:

CTE can be thought of as an alternatives to derived tables (subquery), views, and inline user-defined functions

CTEs are define by the WITH statement and enable you to seperately define a complex sub-select statement separately making your queries more readable/logical.

Some of the most frequent uses of a Common Table Expression include creating tables on the fly inside a nested select, and doing recursive queries. Common Table Expressions can be used for both selects and DML statements. The natural question is, if we have been using TSQL for this long without Common Table Expressions, why start using them now? There are several benefits to learning CTEs. Although new to SQL Server, Common Table Expressions are part of ANSI SQL 99, or SQL3. Therefore, if ANSI is important to you, this is a step closer. Best of all, Common Table Expressions provide a powerful way of doing recursive and nested queries in a syntax that is usually easier to code and review than other methods.

http://www.databasejournal.com/features/mssql/article.php/3502676/Common-Table-Expressions-CTE-on-SQL-2005.htm

The following simple example demonstrates the syntax:

USE AdventureWorks GO  

WITH MyCTE( ListPrice, SellPrice) AS (   

SELECT ListPrice, ListPrice * .95   

FROM Production.Product ) 

SELECT * FROM MyCTE  GO

The above myCTE can then be in place of a table in the immediate next SQL statement.

Above the MAXRECURSION statement limits the number of times the sub-query will run