Skip to main content

​
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.
⌘I