Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Recursive Queries in MS SQL Server

Introduction
The very basics
Infinite looping
Processing trees with recursive CTEs
Processing graphs with recursive ctes
Summary

Instruction

Now, let's discuss the remaining parts of our example query. We'll paste it here again for your convenience:

recursion

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

SELECT
  *
FROM Counter

outside the CTE. This part of the query is called the invocation.

The animation below shows how our final recursive table is constructed.

Recursion query
Restart animation

Technically speaking, recursive CTEs use iterations rather than recursion. Nevertheless, they’re still called recursive.

Exercise

Modify the example query so that it shows the numbers from 20 to 5 (inclusive, in descending order).

Stuck? Here's a hint!

Your anchor member will now be 20, because that's the starting point. Instead of adding one each time, subtract it. Continue the operation as long as the previous value is greater than 5.