Common Table Expressions(CTE)  are a new feature  in Microsoft SQL Server 2005 that offer a more readable form of the derived table that can be declared once and referenced multiple times in a query.

WITH ProductAndCategoryNamesOverTenDollars (ProductName, CategoryName, UnitPrice)

AS

(
   SELECT    p.ProductName,
                     c.CategoryName,
                     p.UnitPrice
  FROM Products p  INNER JOIN Categories c ON c.CategoryID = p.CategoryID
  WHERE p.UnitPrice > 10.0
)

SELECT *
FROM ProductAndCategoryNamesOverTenDollars
ORDER BY CategoryName ASC, UnitPrice ASC, ProductName ASC 

This query creates a CTE named ProductAndCategoryNamesOverTenRuppes that returns the name, category name, and price of those products whose unit price exceeds 10.00.

In short, a Common Table Expression allows us to define a temporary, view-like construct. We start by (optionally) specifying the columns it returns, then define the query.