SQL Server: Generate Numbers Table for a Range of Numbers

If you are in some environments that you do not have permission to create a new numbers table, you can use the Recursive Common Table Expressions (CTE) approach to create a virtual number sequence table. It's like generate_series available in PostgreSQL.

Microsoft SQL Server has set a default value for maximum recursion level to avoid infinite loops.

caution

Be aware that the default value of MAXRECURSION depth option is 100, causing the CTE to terminate when it recurses past 100 levels.

In the following TSQL snippet, if you set @stop_number variable to 1001 without changing the value of MAXRECURSION option to 1001, you will get an error similar to the one below.

danger

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

In TSQL, you can work around this by setting MAXRECURSION to a larger value.

In the snippet, we are adjusting the session value based on the number of iterations required to create number sequence.

It will print 1 to 1001.

To print 1 to 100, simply set @stop_number to 100.

Solution: Recursive CTE

Print 1 to 1001 Using Recursive CTE
-- Adjust lower and upper bound of the number sequence to generate
DECLARE @start_number INT = 1;
DECLARE @stop_number INT = 1001;

WITH cte_print (number)
AS (
-- Anchor member returns the start number
SELECT @start_number
UNION ALL
-- Recursive member that references to the CTE name returns 1 + the previous number
SELECT number + 1
FROM cte_print
WHERE number < @stop_number -- Termination condition for recursive call
)
SELECT *
FROM cte_print
-- Adjust max recursion depth based on the number of iterations we need.
-- Optional if you will not recurse past 100 levels
OPTION (MAXRECURSION 1001);

Solution: Cross Join and Window Function

An alternative option to generate a virtual numbers table is using Cross Joins along with Row_Number() window function.

The following TSQL snippet will support printing a sequence up to 65,536 numbers. You could reduce the number of cross joins used if you don't need a sequence with that many numbers.

Executing the TSQL query will print 1 to 10,000.

Print 1 to 10000 Using Cross Joins
-- Adjust lower and upper bound of the number sequence to generate
DECLARE @start_number INT = 1;
DECLARE @stop_number INT = 10000;

-- With 5 common table expressions, we could generate number sequence from 1 up to 65,536
WITH CTE01 (number) -- 2 Rows
AS (
	SELECT 1

	UNION ALL

	SELECT 1
	),
CTE02 (number) -- 2 x 2 = 4 Rows
AS (
	SELECT 1
	FROM CTE01 AS L
	CROSS JOIN CTE01 AS R
	),
CTE03 (number) -- 4 x 4 = 16 Rows
AS (
	SELECT 1
	FROM CTE02 AS L
	CROSS JOIN CTE02 AS R
	),
CTE04 (number) -- 16 x 16 = 256 Rows
AS (
	SELECT 1
	FROM CTE03 AS L
	CROSS JOIN CTE03 AS R
	),
CTE05 (number) -- 256 x 256 = 65,536 Rows
AS (
	SELECT 1
	FROM CTE04 AS L
	CROSS JOIN CTE04 AS R
	),
Num_Sequence
AS (
	SELECT row_number() OVER (
			ORDER BY number
			) AS number
	FROM CTE05
	)
SELECT *
FROM Num_Sequence
WHERE number BETWEEN @start_number
		AND @stop_number;

Solution: Recursive CTE​

Solution: Cross Join and Window Function​

Solution: Recursive CTE

Solution: Cross Join and Window Function