CTE Query Performance

The following question came up when working on my CTE presentation for SQL Saturday. Does a query that JOINs a CTE to itself execute the CTE query once or twice?

For instance:


 ;WITH deptCTE(id, department, parent)
    AS (SELECT id,department,parent FROM   Departments)
SELECT q1.department,q2.department
  FROM deptCTE q1
 INNER JOIN deptCTE q2 ON q1.id = q2.parent
 WHERE q1.parent IS NULL; 

The following execution plan is produced showing that the Departments table is hit twice with a table scan each time with the same cost.

In the above example the query inside of the CTE gets run twice, and has no performance improvement over using the same query twice as a subquery. But it does clean up the syntax and reduce errors by only having the query in one place.

Download my book for FREE when you sign up for my mailing list.

Receive your free copy today!

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!