Menu
How and why CTE gives a better performance as compared to derived table/ subqueries/ temp table etc. approaches?
Any temporary calculations happens in the temporary database. So if we have a cursor approach, it also creates temporary table/work table in the temporary database and once the operation is over, that work table is destroyed. My understanding of CTE is that, it also does the same(or does it creates temporary result in memory? and hence the performance improvement) Then why is it better than the above approaches like cursor/derived table/ subqueries/ temp table etc.?
473
A CTE is not necessarily better than using a derived table, but does lead to more understandable TSQL code. A CTE is really just shorthand for a query or subquery; something akin to a temporary view. The situation where CTE's might not be the best approach, is when the query plan optimiser gets inaccurate row estimates for the CTE.
In this article, you will learn about the main differences between Temp Table, Table variable and CTE. CTE stands for Common Table Expressions. It was introduced with SQL Server 2005. It is a temporary result set and typically it may be a result of complex sub-query.
Instead of having to declare the same subquery in every place you need to use it, you can use CTE to define a temporary table once, then refer to it whenever you need it. CTE can be more readable: Another advantage of CTE is CTE are more readable than Subqueries.
A CTE can be referenced multiple times in the same query. So CTE can use in recursive query. Derived table can’t referenced multiple times. Derived table can’t use in recursive queries.
A (non-recursive) CTE does not use cursors. It is a set based approach. That's the big difference compared to using cursors. But then that's true of not using cursors in general.
Cursors should be avoided where absolutely possible (as I'm sure we are all aware).
A CTE is not necessarily better than using a derived table, but does lead to more understandable TSQL code. A CTE is really just shorthand for a query or subquery; something akin to a temporary view.
The situation where CTE's might not be the best approach, is when the query plan optimiser gets inaccurate row estimates for the CTE.
Related question: What are the advantages/disadvantages of using a CTE?
181
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
