SQL Server – How does SQL recursion work?

The structure of a recursive query looks quite strange in SQL from other programming languages. Go step by step, and it seems to be falling apart.

Consider the following simple example:

CREATE TABLE #NUMS
(
N BIGINT
)
;

INSERT IN #NUMS
VALUES
(3)
(5)
(7)
;



WITH R AS
(
CHOOSE N FROM #NUMS

UNION ALL

CHOOSE N * N AS N OF R, WHERE N * N <10000000
)
CHOOSE N FROM ORDER N
;

Let's go through it.

First, the anchor element is executed and the result set is stored in R. R is initialized to {3, 5, 7}.

Then the execution falls under UNION ALL and the recursive member is executed for the first time. It is executed on R (ie H. On the R that we currently hold: {3, 5, 7}). This leads to {9, 25, 49}.

What's up with this new result? Does {9, 25, 49} depend on the existing {3, 5, 7}, does the resulting union denote R, and does the recursion continue from there? Or does R redefine it to be just this new result {9, 25, 49} and later perform the union?

Neither option makes sense.

Now if R is {3, 5, 7, 9, 25, 49} and we do the next iteration of recursion, we'll end up with {9, 25, 49, 81, 625, 2401} and we'll lose {3 , 5, 7}.

If R is now just {9, 25, 49}, we have a problem with the wrong name. R is the union of the anchor element result set and all subsequent recursive element result sets. While {9, 25, 49} is only one component of R. It's not the full R we've accumulated so far. Therefore, it makes no sense to write the recursive element as a selection from R.