Common Table Expressions (CTEs) are runtime constructs to derive an inline intermediate result set from a query. This means that a complex T-SQL query can be broken down into simpler T-SQL statements that store intermediate results before joining with other tables or other CTEs that had been previously defined in the T-SQL statement. For example, take the two following queries that can be executed in the AdventureWorks sample database:
WITH Sales_CTE(SalesPersonID, SalesOrderID, SalesYear)AS( SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear FROM Sales.SalesOrderHeader WHERE SalesPersonID IS NOT NULL)SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYearFROM Sales_CTEGROUP BY SalesYear, ...
