How do recursive SQL Querys terminate and how do they get evaluated?

Question

I ran over the following code

I just cannot grasp how the computer would possibly evaluate such code such that cte_count will ever terminate.

In my mind it works like this:

1. cte_count is a table which contains a column called n containing 1 and the result of (Recursive Member)

First of all: Whatis n + 1 supposed to mean? n is the name of a column, as far as I am concerned.

Assuming that n + 1 just increases somehow a counter: Secondly, how will the termination condition ever be checked, when cte_count will be evaluated before.

I hope someone can make clearer how such Queries work.

Answer 1

I just cannot grasp how the computer would possibly evaluate such code such that cte_count will ever terminate.

Well the short answer is that it does not know. The recursive CTE will only recurs maximum of 32,767 times or infinitely. See official microsoft documentation and then throws signal that maximum recursion value is reached. Exact number of maximum recursion can be set using MAXRECURSION property (with 0 meaning infinite).

Yes N is the name of column. And the recursion query part

select n+1  from cte_count where n<50

is a shorthand for

select n+1  as n from cte_count where n<50

in this case as unnamed column in the union all clause takes the same datatype and name as the similarly positioned column in the upper part of the UNION ALL syntax.

so imagine starting the CTE with select 1 as N pushed into a table cte_count with single column N like so

N
1

and then first recursion operates on this table to give result

N
2

These two are union all-ed to give new cte_count table as

N
1
2

next recursion uses the above table as input to give result as

N
2
3

which is union all-ed with existing cte_count table to give updated cte_count as

N
1
2
3