SQL Performance Tuning and Optimization – Where CTEs are stored ?

Well before that let’s first understand what is CTE?

1. A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement.

2. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query.

3. A CTE can also be used for recursion.

4. So you cannot say whether CTE are stored are somewhere or not? The answer to this question is check the execution plan of the query. Just as with any other query, look at the query optimizer to see how it’s resolving the CTE. Now, in the case of a recursive CTE, you may see the optimizer choose to use table spool operations. In that case, yeah, it’s going to be probably putting stuff into tempdb. Again, look to the execution plan to see how the query is resolved.

Example of a Recursive CTE – storing data in a temp DB [Table Spool]

--

;WITH CTE(EmpName , EmpId, Level,FullyQualifiedName) AS (
     Select E.EmpName, E.EmpID, 0 Level
     , Cast('.'+E.EmpName+'.' as Varchar(MAX)) FullyQualifiedName         
     From Employees E Where E.ReportsTo IS NULL
     UNION ALL
     Select E.EmpName, E.EmpID, c.Level + 1 , c.FullyQualifiedName+'.'+E.EmpName+'.' FullyQualifiedName  
     From Employees E INNER JOIN CTE c on c.EmpID = e.ReportsTo 
)
SELECT SPACE(LEVEL*4) + H.EmpName Hierarchy,SPACE(LEVEL*4) + CAST(H.EmpID AS VARCHAR(MAX)) Hierarchy , * FROM CTE H 
ORDER BY H.FullyQualifiedName

--

--

Execution Plan

Pawan Khowal – RecursiveCTEs – Stored data in tempDB [TableSpools]

--

WITH CTE AS 
(
	SELECT * FROM Employees
)
SELECT * FROM CTE b INNER JOIN CTE a on a.EmpID = b.EmpID 
GO
 

--

--

Execution Plan

Pawan Khowal – CTEs – Not storing anything anywhere

So all in all we can say CTEs are not stored anywhere but there are exceptions where data needs to be stored in a tempdb as a part performance optimization technique, so optimizer stored data in tempDB. I hope you have enjoyed the article. In the next post I will explain an interview question asked to me in one of technical interviews I faced recently. Thanks for reading !

-Pawan Khowal

MSBISkills.com