Comparison: CTE vs CURSOR

Question

I do not know if this is a question that many ask, but I have always had it in mind almost always.

Usually, when I need to deliver a script corrective to a customer, used only to be executed once, I use a CURSOR, perhaps because it is more readable to those who do not know much about TSQL and because it is, in a way, simpler to maintain (recursiveness can often "complicate").

But in development I try, whenever possible, to use CTE because it is "what should be used", according to experts in this field.

So I question: CTE or CURSOR?

I know that CTE is supposed to offer more performance in relation to the CURSOR, but it will be enough to use it to the detriment of the other option?

The issue of recursion, as I mentioned above, can often complicate a simple process, and can even make it very difficult to maintain a script of various levels in a more complex scenario.

In CTE it is not possible for us to understand exactly what is done in between, while with a CURSOR we managed to put a PRINT or SELECT with important or useful information in a debug or validation of results.

---

In short, use a CURSOR using temporary (for example), or even a cycle WHILE, is so much worse than using a CTE?

Of course it must depend on the scenario, but therefore, where we should use one and the other?

Answer 1

Good remarks on the question. In fact the cursor is the more imperative form, despite still being placed declarative form in the code. CTE is more functional. The reason we find it more readable is that we program imperative all the time.

CTE can offer more performance, but this is not automatic and in fact because it is something less common for many people can make mistakes without noticing and getting worse. But it can do optimizations that you probably wouldn’t think or wouldn’t be able to decide properly otherwise. There are cases you can provide better algorithm.

As far as I know SQL Server will always play the cursor data on disk, and in conforming it may be that CTE is faster in all simpler situations because it remains in memory, but it is an implementation failure, and it may change one day. I found a comparison in a specific scenario.

Remembering that CTE is usually incompatible between databases. Not that the cursor is so more standardized. There are cases where SGDB does not have one or the other and you are left with no choice. It is not the case of SQL Server, of course. And if you chose him in question I imagine that question is not very relevant.

Because CTE is an abstraction it can be more difficult to understand what happens there and how to debug the result, you have to fight with the API instead of fighting with the algorithm. For simple cases it may be easier, but for complex cases the CTE can be torturous. With CTE you give up control, which may or may not be good.

Try doing without CTE in tree models. Just as recursion abuse is bad, CTE abuse can be bad too. But just like recursion is best in several cases, the CTE also.

In general I think you should wear what you feel most comfortable, most readable. Performance should be measured, if you notice something worse, change, the way you feel most comfortable. Just don’t leave CTE aside, to darlings can make the code at least more expressive.

But if you are an SQL "programmer", you should opt for CTE. It’s the most idiomatic way to do it, it’s more expensive than SQL. The same thing I’m talking about about CTE goes for SQL. If you have a way, and some Dbs have this way, to access without using SQL you would make it simpler, and possibly readable to you, imperative way.

Doing as subquery makes you more comfortable? CTE is just that in a more "disguised way".

There’s no magic answer to which one to use. It is so wrong to say that you should only use cursor as who says, and many people say, that you should only use CTE.