====== Numbers table ======

A [[http://www.sqlservercentral.com/articles/T-SQL/62867/|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]].

===== Creation =====
<code tsql>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]
</code>

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 :-(.

===== Or try this =====
<code tsql>
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]
</code>

===== Numbers CTE =====
If you don't want a permanent numbers table you can derive a list of numbers on-the-fly in a CTE:
<code tsql>
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
</code>

You can generate numbers without a table source using a recursive CTE (from https://sqlhints.com/tag/maxrecursion-sql-server/):
<code tsql>
WITH n AS (
  SELECT  1 AS N
  UNION ALL
  SELECT N + 1 FROM n  
  WHERE N < 32000
)
SELECT * FROM n
OPTION (MAXRECURSION 0)
</code>

{{tag> tsql development}}