--select * from dbo.GetNums(1, 100000)
--PRE 2012
/* Based on the article "Virtual Auxiliary Table of Numbers" by Itzik Ben-Gan at http://sqlmag.com/sql-server/virtual-auxiliary-table-numbers */
CREATE FUNCTION dbo.GetNums(@low AS BIGINT, @high AS BIGINT) RETURNS TABLE
AS
RETURN
  WITH
    L0   AS (SELECT c FROM (VALUES(1),(1)) AS D(c)),
    L1   AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
    L2   AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
    L3   AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
    L4   AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
    L5   AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
    Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum
            FROM L5)
  SELECT TOP(@high - @low + 1) @low + rownum - 1 AS n
  FROM Nums
  ORDER BY rownum;
GO

--SELECT * FROM dbo.GetNums1(1, 1000000)
--2012 ALT1
/* Based on the article "Virtual Auxiliary Table of Numbers" by Itzik Ben-Gan at http://sqlmag.com/sql-server/virtual-auxiliary-table-numbers */
CREATE FUNCTION dbo.GetNums1(@low AS BIGINT, @high AS BIGINT) RETURNS TABLE
AS
RETURN
  WITH
    L0   AS (SELECT c FROM (VALUES(1),(1)) AS D(c)),
    L1   AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
    L2   AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
    L3   AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
    L4   AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
    L5   AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
    Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum
            FROM L5)
  SELECT @low + rownum - 1 AS n
  FROM Nums
  ORDER BY rownum
  OFFSET 0 ROWS FETCH FIRST @high - @low + 1 ROWS ONLY;
GO


--SELECT * FROM dbo.GetNums2(1000000)
--2012 ALT2 ADD MORE REFERENCES TO SYS.OBJECTS AS NEEDED
CREATE FUNCTION  dbo.GetNums2(@N AS BIGINT) RETURNS TABLE
AS
RETURN
SELECT TOP (@N) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS I
    FROM SYS.OBJECTS S1
	CROSS JOIN SYS.OBJECTS S2
	CROSS JOIN SYS.OBJECTS S3
	CROSS JOIN SYS.OBJECTS S4
	CROSS JOIN SYS.OBJECTS S5
GO