A CTE is composed of an expression name, optional column list, and a defining query. Once defined, the CTE may be referenced in the executing SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement for example: use Northwind; with Customers_CTE as ( select ContactName,CompanyName from Customers ) select * from Customers_CTE the beautiful thing about CTEs is that you can use them to create recursive queries say you have the following table Employees(EmpID,Name,ManagerID) and you want to select each employee and his level in the employees hierarchy (with 0 as the top level) this can be accomplished with the following CTE With Emps_CTE (EmpID,Name,Level) As ( SELECT EmpID,Name, 0 AS Level FROM Employees WHERE ManagerID IS NULL Union All Select Employees.EmpID,Employees.Name,Emps_CTE.Level+1 as Level FROM Emps_CTE join Employees on Emps_CTE.EmpID=Employees.ManagerID ) Select * FROM Emps_CTE Note that the defining query must contain at least two SELECT statements combined by a UNION ALL op