Now, let's discuss the remaining parts of our example query. We'll paste it here again for your convenience:
First of all, note that we used
UNION ALL to join the anchor member (
SELECT 1) with all further recursive steps (
SELECT PreviousValue + 1). In SQL Server, you can use only
UNION ALL in a recursive CTE. In order to remove values that aren't unique, you should use
DISTINCT in the invocation part.
Once the recursive CTE is completed and our temporary table is created, we need to query this table to actually see any results. That's why we put
outside the CTE. This part of the query is called the invocation.
The animation below shows how our final recursive table is constructed.
Technically speaking, recursive CTEs use iterations rather than recursion. Nevertheless, they’re still called recursive.