How many times the T-SQL inside the CTE is executed?

Introduction

Common Table Expression (CTE) has become very popular these days. However, many users of CTE still have a myth that the t-sql written inside the CTE will be executed only once irrespective of the number of times it is referred in the subsequent CTEs or the related query.
However, this is not correct. It is a pure misconception. And let’s try to prove this with a simple example.

Proof
The Query

;WITH CTE1 AS (

SELECT

* 

FROM

dbo.Employees

),CTE2 AS (

SELECT

*

FROM

CTE1

)

SELECT

C1.EmployeeID,

C1.Name,

C2.EmployeeID,

C2.Name,

C3.EmployeeID,

C3.Name

FROM

CTE1 C1

INNER JOIN CTE2 C2

ON C2.EmployeeID = C1.EmployeeID

INNER JOIN CTE1 C3

ON C3.EmployeeID = C1.EmployeeID

Execution Plan



Please have a look at the execution plan. It clearly shows that even though the t-sql to fetch the data from the table is written only once inside CTE1, the Scan is done thrice. This is because CTE1 is referred thrice in the final query -

1. Two times CTE1 is directly used.
2. Once CTE1 is used indirectly via CTE2.

Conclusion
The CTE should be thought of a view that is defined for the current query only. At the time of execution, the query optimizer will replace all the direct/indirect use of CTE with the actual query in the same way as it does for Views.