TSQL -> Create function that returns tally table of a given size

Tally table is extremly usefull for a number of SQL queries solutions. Following script is adopted from Itzik Ben-Gan's article "Virtual Auxiliary Table of Numbers" http://www.sqlmag.com/article/sql-server/virtual-auxiliary-table-of-numbers.aspx .
It can be very helpfull when you have no permission to store a real Tally table in the current database, or you do not want to be aware about current database context, or because of any other reason. This solution uses CTE expression and works really fast even if we will try to get many millions of sequential numbers.

-- ---- tally table generator that uses CTE expression -----

DECLARE @max_rows AS BIGINT;
SET @max_rows = 50;-- put here any number you want

WITH
  lv0 AS (SELECT 0 AS g UNION ALL SELECT 0),
  lv1 AS (SELECT 0 AS g FROM lv0 AS a CROSS JOIN lv0 AS b), -- 4
  lv2 AS (SELECT 0 AS g FROM lv1 AS a CROSS JOIN lv1 AS b), -- 16
  lv3 AS (SELECT 0 AS g FROM lv2 AS a CROSS JOIN lv2 AS b), -- 256
  lv4 AS (SELECT 0 AS g FROM lv3 AS a CROSS JOIN lv3 AS b), -- 65,536
  lv5 AS (SELECT 0 AS g FROM lv4 AS a CROSS JOIN lv4 AS b), -- 4,294,967,296

  Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM lv5)

SELECT TOP (@max_rows) n FROM Nums ORDER BY n;


This solution can be enveloped by udf function that returns table. Function can be called when ever you need to get sequential numbers.
-- ---- function that returns tally table of the given size -----

CREATE FUNCTION dbo.get_numseq (@max_rows AS BIGINT) RETURNS TABLE
AS
-- returns up to 4,294,967,296 records
WITH
  lv0 AS (SELECT 0 AS g UNION ALL SELECT 0),
  lv1 AS (SELECT 0 AS g FROM lv0 AS a CROSS JOIN lv0 AS b),
  lv2 AS (SELECT 0 AS g FROM lv1 AS a CROSS JOIN lv1 AS b),
  lv3 AS (SELECT 0 AS g FROM lv2 AS a CROSS JOIN lv2 AS b),
  lv4 AS (SELECT 0 AS g FROM lv3 AS a CROSS JOIN lv3 AS b),
  lv5 AS (SELECT 0 AS g FROM lv4 AS a CROSS JOIN lv4 AS b),

  Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM lv5)

  SELECT TOP (@max_rows) n FROM Nums ORDER BY n
GO


Test it:
SELECT n FROM get_numseq(50)

Results:
n
1
2
3
4
5
...



sqlexamples.info