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 (
),CTE2 AS (
INNER JOIN CTE2 C2
ON C2.EmployeeID = C1.EmployeeID
INNER JOIN CTE1 C3
ON C3.EmployeeID = C1.EmployeeID
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,
is done thrice.
This is because CTE1 is referred thrice in the final query -
- Two times CTE1 is directly used.
- Once CTE1 is used indirectly via CTE2.
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.