Geek Answers Handbook

Why is CTE better than cursor / view / subquery / temp table etc.?

How and why does CTE give better performance compared to views / subqueries / temp tables, etc.?

Any temporary calculations occur in a temporary database. Therefore, if we have a cursor approach, it also creates a temporary table / work table in a temporary database, and as soon as the operation is completed, this worksheet will be destroyed. My understanding of CTE is that it also does the same (or creates a temporary result in memory? And, therefore, improves performance). Then why is it better than the aforementioned approaches like table / cursor subqueries / views etc.?

+5
source share
1 answer

A (non-recursive) CTE does not use cursors. This is an integrated approach. This is a big difference compared to using cursors. But then it concerns not using cursors at all.

Cursors should be avoided where absolutely possible (I'm sure we all know).

CTE is not necessarily better than using a view, but it leads to a more understandable TSQL code. CTE really just shortens the query or subquery; something like a temporary look.

Related question: What are the advantages / disadvantages of using CTE?

+7
source

More articles:


All Articles