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.