Generate an arbitrary range in T-SQL UDF.

generate.sql

DBCC DROPCLEANBUFFERS;
CHECKPOINT;
DBCC FREEPROCCACHE WITH no_infomsgs;
GO

CREATE OR ALTER FUNCTION Range (@start BIGINT, @end BIGINT)
RETURNS @table TABLE (Id BIGINT) AS
BEGIN

  -- Jerry Nixon

  DECLARE @sqr BIGINT = SQRT(SQRT(SQRT(@end)));
  SET @start = @start - 1;

  WITH [generator] (Id) AS 
  (
    SELECT @start
    UNION ALL
    SELECT [Id] + 1 FROM generator WHERE [Id] < @sqr
  )
  , [combined] AS
  (
    SELECT TOP (@end) a.Id 
    FROM [generator] AS a
        ,[generator] AS b
        ,[generator] AS c
        ,[generator] AS d
        ,[generator] AS e
        ,[generator] AS f
        ,[generator] AS g
        ,[generator] AS h
  )
  INSERT INTO @table (Id)
  SELECT @start + ROW_NUMBER() OVER (ORDER BY Id)
  FROM [combined]
  OPTION (MAXRECURSION 0)

  RETURN;
END
GO

DECLARE @end BIGINT = 2000000;
PRINT FORMAT(@end, '0,0 ROWS REQUESTED')

SET STATISTICS TIME ON

SELECT FORMAT(COUNT(1), '0,0 ROWS RETURNED') FROM Range(1, @end);

SET STATISTICS TIME OFF

Pretty fast.