Numbers table

A numbers table is a well-indexed, single-column table containing every positive integer in a given range. Numbers (sometimes called 'tally') tables can be used to drive set-based approaches to problems that might naively be solved using less-efficient iterative methods, e.g.Sentence case formatting.

IF OBJECT_ID('dbo.Numbers') IS NOT NULL
  DROP TABLE dbo.Numbers
 
CREATE TABLE dbo.Numbers (
  N SMALLINT,
  CONSTRAINT pk_Numbers PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
)
 
INSERT INTO dbo.Numbers ( N )    
SELECT TOP 32000
  ROW_NUMBER() OVER(ORDER BY c1.[object_id])
FROM sys.all_columns c1
  CROSS JOIN sys.all_columns  c2
 
GRANT SELECT, REFERENCES 
ON dbo.Numbers 
TO [public]

In this example the N column datatype is SMALLINT to reduce the size of the table, but you may need to change this if you need a larger integer range (to INT or BIGINT). Notice that numbers are generated by using ROW_NUMBER() over an arbitrary SELECT statement - the SELECT target isn't important, as long as the result set contains the necessary numbers of rows.

We could do this using a WHILE loop, but it's much slower. You may have no choice on SQL 2K platforms .

IF OBJECT_ID('dbo.BigNumbers') IS NOT NULL
  DROP TABLE dbo.BigNumbers
 
CREATE TABLE dbo.BigNumbers (
  N INT IDENTITY(1,1) NOT NULL
, A BIT
)
 
DECLARE @target INT = 1000000
 
WHILE (
  SELECT COUNT(*)
  FROM dbo.BigNumbers 
) < @target
BEGIN 
 
  INSERT INTO dbo.BigNumbers ( 
    A 
  )    
  SELECT TOP 100000
    0
  FROM sys.all_columns c1
    CROSS JOIN sys.all_columns c2
 
END
 
ALTER TABLE dbo.BigNumbers
DROP COLUMN A
 
ALTER TABLE dbo.BigNumbers
ADD CONSTRAINT PK__dbo_BigNumbers PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
 
GRANT SELECT, REFERENCES 
ON dbo.BigNumbers 
TO [public]

If you don't want a permanent numbers table you can derive a list of numbers on-the-fly in a CTE:

WITH n AS (
  SELECT TOP 32000
    ROW_NUMBER() OVER(ORDER BY c1.[object_id]) AS N
  FROM sys.all_columns c1
    CROSS JOIN sys.all_columns  c2
)
SELECT *
FROM n

You can generate numbers without a table source using a recursive CTE (from https://sqlhints.com/tag/maxrecursion-sql-server/):

WITH n AS (
  SELECT  1 AS N
  UNION ALL
  SELECT N + 1 FROM n  
  WHERE N < 32000
)
SELECT * FROM n
OPTION (MAXRECURSION 0)