# How to generate numbers table in SQL?

## 1. Generate Number table with cross join and row_number function

``````CREATE TABLE numbers (n INT NOT NULL PRIMARY KEY);
GO

;WITH L0   AS (SELECT 1 AS col UNION ALL SELECT 1 AS col),
L1   AS (SELECT 1 AS col FROM L0 AS A, L0 AS B), --4
L2   AS (SELECT 1 AS col FROM L1 AS A, L1 AS B), --16
L3   AS (SELECT 1 AS col FROM L2 AS A, L2 AS B),--256
L4   AS (SELECT 1 AS col FROM L3 AS A, L3 AS B),--65536
L5   AS (SELECT 1 AS col FROM L4 AS A, L4 AS B),--4294967296
NUM  AS (SELECT ROW_NUMBER() OVER(ORDER BY col) AS n FROM L5)
INSERT numbers (n)
SELECT n
FROM  NUM
WHERE n <= 100000;
``````

In this method, we are squaring the number of records from the previous CTE and generate a new CTE. In this way, we are able to generate a table with 4^32 records. Then we are using the ROW_NUMBER function to generate the numbers and limit the numbers based on the maximum number that we want to generate. This solution is originally created by Itzik Ben-Gan

``````-- Number of records in each CTE expression above.
SELECT COUNT(1) FROM L0  --2
SELECT COUNT(1) FROM L1  --4
SELECT COUNT(1) FROM L2  --16
SELECT COUNT(1) FROM L3  --256
SELECT COUNT(1) FROM L4  --65536
SELECT COUNT(1) FROM L5  --4294967296
``````

## 2. Use generate_series() built-in function

This function is relatively new and it is available in SQL Server 2022 preview version.

``````-- SQL Server 2022

-- generate number from 1 to 100
SELECT value FROM GENERATE_SERIES(1, 100);

--generate odd numbers from 1 to 99 (i.e., 1, 3, 5, 7 ...99)
SELECT value FROM GENERATE_SERIES(1, 99, 2);

--generate even numbers from 1 to 100 (i.e., 2, 4, 6, 8 ...100)
SELECT value FROM GENERATE_SERIES(2, 100, 2);
``````

I suggest you to read Erland Sommarskog's article on table of numbers for other interesting solutions.