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. 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
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|
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…