SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
print('The inline table valued funtion (itvf) fnNumbers returns bigint and can generate n of up to 4^16')
The inline table valued funtion (itvf) fnNumbers returns bigint and can generate n of up to 4^16


create function [dbo].[fnNumbers](
  @zero_or_one   bit, 
  @n             bigint)
returns table with schemabinding as return
with n(n) as (select null from (values (1),(2),(3),(4)) n(n))
select 0 n where @zero_or_one = 0
union all
select top(@n) row_number() over(order by (select null)) n
from n na, n nb, n nc, n nd, n ne, n nf, n ng, n nh,
     n ni, n nj, n nk, n nl, n nm, n np, n nq, n nr;
print('The inline table valued funtion (itvf) fnTally returns bigint and can generate n of up to 4^16')
The inline table valued funtion (itvf) fnTally returns bigint and can generate n of up to 4^16


CREATE FUNCTION [dbo].[fnTally]
/**********************************************************************************************************************
    Jeff Moden Script on SSC: https://www.sqlservercentral.com/scripts/create-a-tally-function-fntally
**********************************************************************************************************************/
        (@ZeroOrOne BIT, @MaxN BIGINT)
RETURNS TABLE WITH SCHEMABINDING AS 
 RETURN WITH
  H2(N) AS ( SELECT 1 
               FROM (VALUES
                     (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    )V(N))            --16^2 or 256 rows
, H4(N) AS (SELECT 1 FROM H2 a, H2 b) --16^4 or 65,536 rows
, H8(N) AS (SELECT 1 FROM H4 a, H4 b) --16^8 or 4,294,967,296 rows
            SELECT N = 0 WHERE @ZeroOrOne = 0 UNION ALL
            SELECT TOP(@MaxN)
                   N = ROW_NUMBER() OVER (ORDER BY N)
              FROM H8
;
create table performance_metrics(
  pm_id         int identity(1, 1) primary key not null,
  test_item     nvarchar(256) not null,
  start_dt      datetime2(7) not null,
  end_dt        datetime2(7) null,
  ms_diff       as datediff(ms, start_dt, end_dt) persisted);
insert performance_metrics(test_item, start_dt, end_dt) 
values ('fnNumbers', sysutcdatetime(), null);

declare 
  @test_zero_or_one         bit=1,
  @test_n                   int=1000;

create table #test_fnNumbers_row_counts(row_count     int not null);

set statistics io on;
set statistics time on;

insert #test_fnNumbers_row_counts(row_count)
select count(*) from dbo.fnNumbers(@test_zero_or_one, @test_n) union all
select count(*) from dbo.fnNumbers(@test_zero_or_one, @test_n) union all
select count(*) from dbo.fnNumbers(@test_zero_or_one, @test_n) union all
select count(*) from dbo.fnNumbers(@test_zero_or_one, @test_n) union all
select count(*) from dbo.fnNumbers(@test_zero_or_one, @test_n) union all
select count(*) from dbo.fnNumbers(@test_zero_or_one, @test_n) union all
select count(*) from dbo.fnNumbers(@test_zero_or_one, @test_n) union all
select count(*) from dbo.fnNumbers(@test_zero_or_one, @test_n) union all
select count(*) from dbo.fnNumbers(@test_zero_or_one, @test_n) union all
select count(*) from dbo.fnNumbers(@test_zero_or_one, @test_n) ;

set statistics io off;
set statistics time off;

update performance_metrics
set end_dt=sysutcdatetime()
output  concat(inserted.test_item, ' inserted 10x row counts (n=1,000) in ', inserted.ms_diff, ' milliseconds') [Performance Summary]
where test_item='fnNumbers';


Performance Summary


fnNumbers inserted 10x row counts (n=1,000) in 63 milliseconds


SQL Server parse and compile time: 
   CPU time = 46 ms, elapsed time = 46 ms.
Table '#test_fnNumbers_row_counts__________________________________________________________________________________________0000000001F3'. Scan count 0, logical reads 10, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.


insert performance_metrics(test_item, start_dt, end_dt) 
values ('fnTally', sysutcdatetime(), null);

declare 
  @test_zero_or_one         bit=1,
  @test_n                   int=1000;

create table #test_fnNumbersRC_row_counts(row_count     int not null);

set statistics io on;
set statistics time on;

insert #test_fnNumbersRC_row_counts(row_count)
select count(*) from dbo.fnTally(@test_zero_or_one, @test_n) union all
select count(*) from dbo.fnTally(@test_zero_or_one, @test_n) union all
select count(*) from dbo.fnTally(@test_zero_or_one, @test_n) union all
select count(*) from dbo.fnTally(@test_zero_or_one, @test_n) union all
select count(*) from dbo.fnTally(@test_zero_or_one, @test_n) union all
select count(*) from dbo.fnTally(@test_zero_or_one, @test_n) union all
select count(*) from dbo.fnTally(@test_zero_or_one, @test_n) union all
select count(*) from dbo.fnTally(@test_zero_or_one, @test_n) union all
select count(*) from dbo.fnTally(@test_zero_or_one, @test_n) union all
select count(*) from dbo.fnTally(@test_zero_or_one, @test_n)
option (maxrecursion 0);

set statistics io off;
set statistics time off;

update performance_metrics
set end_dt=sysutcdatetime()
output  concat(inserted.test_item, ' inserted 10x row counts (n=1,000) in ', inserted.ms_diff, ' milliseconds')  [Performance Summary] 
where test_item='fnTally';


Performance Summary


fnTally inserted 10x row counts (n=1,000) in 281 milliseconds


SQL Server parse and compile time: 
   CPU time = 281 ms, elapsed time = 281 ms.
Table '#test_fnNumbersRC_row_counts________________________________________________________________________________________0000000001F4'. Scan count 0, logical reads 10, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.