Wednesday 7 December 2011

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


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.

No comments:

Post a Comment