Is a SQL Server recursive CTE considered a loop?

Question

I was under the impression that recursive CTEs were set based, but in a recent SO post someone mentioned that they are loops.

Are recursive CTEs set based? Am I wrong to assume that a set based operation cannot be a loop?

Answer 1

If it is recursive it is still considered a loop. Although one statement is set based, calling it over and over can be considered a loop. This is an argument about the definition or wording based on the context being used. They are set based statements but the processing is considered in simple terms a looping process.

For those interested here is a nice little write up about performance with CTE's:

http://explainextended.com/2009/11/18/sql-server-are-the-recursive-ctes-really-set-based/