Is there a loop loop behind the code of a CTE?

Asked

Viewed 85 times

3

I’m racking my brain to understand how such a simple command can generate values from 1 to 100.

Is there a loop behind a CTE? For in the code below there is a loop WHERE and not a WHILE.

WITH CTE_EXEMPLO AS 
(
SELECT 1 AS QNT
UNION ALL 
SELECT QNT + 1 FROM CTE_EXEMPLO WHERE QNT < 100
)

SELECT * FROM CTE_EXEMPLO
sql loop characteristic-language tsql

  • By completing the @Maniero response, always remember to search which database you are applying to CTE. Each engine of each bank works in one way and the performance can vary greatly may, in some cases, not be the best output using CTE’s.

  • @Douglasvieira The code you posted is an example of recursive CTE.

1 answer

3


It’s not just CTE, it’s all SQL. SQL is a language (not programming as some think) declarative, IE, you say what you want and is the problem of engine who’s behind her working it out the best way he can. So he sort of interprets what his code wants and tells the computer to run, unlike most traditional programming languages that are imperative the code says more exactly what to do than it wants.

Just making it clear that more imperative codes can become somewhat declarative, from simple abstraction to stronger syntax and abstractions that really change the way you do it all.

So in a way it has a loop yes, but well in a way, because the way it performs is the problem of engine And if you think about it, the concept of a loop is already abstract, a computer doesn’t understand that, so there’s a repetition of execution, as happens all the time in everything that you do and you’re not even noticing. Somehow this will be interpreted and executed through a rather complex repetition.

The SELECT simple will already make a "loop" somehow in most cases (the first is one that won’t do it, doesn’t have multiple data to evaluate). This CTE itself has no tie, what’s in it is that it has.

That’s why I tell people to learn how the computer works, how the computer works, how deep, and then going up the abstractions on top of it, this gives a very big force to program well. I do not believe in programmers who do not know this, even some experienced and who are considered good by many, if you do not know this I guarantee that they are overvalued, at least more than they should.

  • In this case I should study the SQL Server engine?

  • I don’t know, I don’t know your need.

  • I just wanted to understand end to end how this command is executed.

  • You have to start with the fundamentals of computing, there are no shortcuts, otherwise you will learn more or less and serve for nothing, or even worse because you will create a lot of myths by understanding everything by half.

Browser other questions tagged sql loop characteristic-language tsql

You are not signed in. Login or sign up in order to post.