http://www.sqlservercentral.com/blogs/juggling_with_sql/2011/12/15/how-many-times-the-t-sql-inside-the-cte-is-executed/

Printed 2014/07/23 12:51PM

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

By vinaypugalia, 2011/12/15

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

CTE

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.

Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.