Take the 2-minute tour ×

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?

share|improve this question
Related answer –  Martin Smith Oct 19 '11 at 18:03
+1 as it is still an excellent question. –  JonH Oct 19 '11 at 18:07
Declarative vs procedural might be a more useful and meaningful distinction to make? –  Martin Smith Oct 19 '11 at 18:52
add comment

2 Answers

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:


share|improve this answer
add comment

They are set based. Recursive sets are still sets.

But all set operations are, if you look with a powerful enough magnifier glass, loops. Ultimately the code runs on CPUs and CPUs execute a stream of serial instructions that operate on discrete regions of memory. In other words, there is no set oriented hardware. Being 'set oriented' is a logical concept. The fact that all SQL operations are ultimately implemented using some form of a loop is an implementation detail.

share|improve this answer
Indeed, even Itzik Ben Gan's undoubtedly set based numbers table generator is still implemented as a bunch of nested loops in the query plan. –  Martin Smith Oct 19 '11 at 18:08
add comment

Your Answer


