Common Table Expressions (CTEs)

CTE introduced 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 and we can used for two things:
1. Efficiently executing a recursive queries
2. Return a temporary result set when nested inside another SQL statement
Return a Temporary result set: Let's start by an example to understand the syntax:



So from the above example, CTE allow us to define temporary result set within execution statement with readable syntax. The CTE syntax contains two parts:
1. CTE name and its columns alias(optional)
2. query within parentheses that produce the temporary result set

Recursive Query:
A recursive CTE expands the definition of the table expression and consists of two parts:
An anchor query, which is the source of the recursion, along with a UNION ALL statement and a second query, which recurses across the anchor query.
let’s see an example. If I have Employees table which has these columns (EmpId,Name ,ReportsTo) and ReportsTo column is a foreign key field that refers to the primary key field EmpId and I want to know the manager of each employee and at which level



The result set of the above query: