A CTE is a temporary result set and are similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. A CTE is generally considered to be more readable than a derived table and does not require the extra effort of declaring a Temp Table . CTE is more powerful than a derived table as it can also be self-referencing, or even referenced multiple times in the same query.
CTE defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement
CTE can be referenced multiple ties in the same statement
AS ( SELECT EmpID, FirstName, LastName, ManagerID, 1 Level
WHERE ManagerID IS NULL
SELECT EmpID, FirstName, LastName, ManagerID, Level+1
INNER JOIN EmpCTE ON Employee.ManagerID = EmpCTE.EmpID
WHERE Employee.ManagerID IS NOT NULL )