T-SQL: How to Generate Numbers from 1 to 10 Without Using a Loop

Instead of relying on traditional SQL loops like WHILE, we’re going to use a Recursive CTE (Common Table Expression) to generate numbers from 1 to 10. A recursive CTE allows a query to repeatedly reference itself — behaving like a loop, but in a set-based, declarative way.

This approach is clean, elegant, and avoids procedural logic entirely. Let’s see how recursion can help us build a number series with nothing but pure SQL.

Below is the Code for generating 1 to 10 Numbers with Recursive CTE:

WITH Orders(ID) AS (
   
--start with Order ID - 1
SELECT 1                  
UNION ALL
-- Recursive Order: add 1 each time
SELECT ID + 1            
FROM Orders
-- End condition
WHERE ID < 10           
)
SELECT ID FROM Orders
-- Prevents infinite recursion
OPTION (MAXRECURSION 10);    

Query Execution Explanation:

• The anchor member starts by selecting the value 1.
• UNION ALL merges the anchor with the recursive member.
• Recursive member selects ID + 1 from Orders.
• Recursion continues as long as ID < 10.
• Final SELECT pulls all IDs from the CTE Orders.
• OPTION (MAXRECURSION 10) caps recursion depth to avoid infinite loops.