> ## Documentation Index
> Fetch the complete documentation index at: https://rajanand.org/llms.txt
> Use this file to discover all available pages before exploring further.

# How to generate numbers table in SQL?

## 1. Generate Number table with cross join and row\_number function

```sql theme={"system"}
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](https://twitter.com/itzikbengan)

```sql theme={"system"}
-- 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](https://docs.microsoft.com/en-us/sql/t-sql/functions/generate-series-transact-sql?view=sql-server-ver16) preview version.

```sql theme={"system"}
-- 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](https://www.sommarskog.se/Short%20Stories/table-of-numbers.html) article on table of numbers for other interesting solutions.