26 performance tuning questions and solutions, Avoid SQL Server functions in the WHERE clause for Performance, Avoid Using Function in WHERE Clause – Scan to Seek, Avoid Using Functions in WHERE Clause tutorial and example, Functions in the WHERE Clause, How to avoid convert function in the where clause, How to tune SQL queries, Increase SQL Server performance avoid using functions in Where Clause, Interview questions for SQL Server Performance Tuning, Looking for SQL Optimization Interview Questions, performance sql server, Performance tips for faster SQL queries, Performance Tuning, Performance Tuning for SQL Server, Query Optimization, Query Performance Tuning, sql - How do I avoid functions like UPPER in where clause, SQL Complex Queries, SQL Optimization Interview Questions, sql performance, sql performance and tuning, sql performance explained pdf, sql performance tips, SQL Performance Tuning, sql performance tuning and optimization, SQL Performance Tuning and Optimization - Where CTEs are stored ?, sql performance tuning interview questions, sql performance tuning tips, SQL Query Optimizer, SQL Query Tuning or Query Optimization, SQL Server 2008 Query Performance Tuning Distilled, SQL SERVER Interview questions, SQL server optimization interview questions and answers, sql server performance query, sql server performance slow, SQL Server Performance Tuning, SQL Server Performance Tuning Tips, SQL SERVER Tips, SQL Tuning Overview, SQL Where Clause Performance, T-SQL Best Practices - Don't Use Scalar Value Functions in Where Clause, Tips for SQL Database Tuning and Performance, Top 10 performance tuning tips for relational databases, WHERE Clause Functions: A Word of Caution, Where CTEs are stored ?</, Which one is better for performance Table Variables or Temp Tables – Only in terms of performance optimization?
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 PlanExample of a CTE – not storing anything in a temp DB
-- WITH CTE AS ( SELECT * FROM Employees ) SELECT * FROM CTE b INNER JOIN CTE a on a.EmpID = b.EmpID GO -- --
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 !