WITH   cte_increment
AS     (SELECT 1 AS nbr		-- anchor member
        UNION ALL
        SELECT nbr + 1		-- recursive member
        FROM   cte_increment
        WHERE  nbr < 10		-- condition to stop
       )
SELECT nbr					-- the result query
FROM   cte_increment
--Option (Maxrecursion 0);