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) fnNumbersRC returns bigint and can generate n of up to 4^16')
The inline table valued funtion (itvf) fnNumbersRC returns bigint and can generate n of up to 4^16


create function [dbo].[fnNumbersRC](
  @zero_or_one   bit, 
  @n             bigint)
returns table with schemabinding as return
with numbers(n) as (
    select cast(@zero_or_one as bigint) n
    union all
    select n+1
    from numbers
    where n<@n)
select n from numbers;
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) 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) 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 20x row counts (n=1,000) in ', inserted.ms_diff, ' milliseconds') [Performance Summary]
where test_item='fnNumbers';


Performance Summary


fnNumbers inserted 20x row counts (n=1,000) in 125 milliseconds


SQL Server parse and compile time: 
   CPU time = 124 ms, elapsed time = 124 ms.
Table '#test_fnNumbers_row_counts__________________________________________________________________________________________0000000001CD'. Scan count 0, logical reads 20, 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 ('fnNumbersRC', 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.fnNumbersRC(@test_zero_or_one, @test_n) union all
select count(*) from dbo.fnNumbersRC(@test_zero_or_one, @test_n) union all
select count(*) from dbo.fnNumbersRC(@test_zero_or_one, @test_n) union all
select count(*) from dbo.fnNumbersRC(@test_zero_or_one, @test_n) union all
select count(*) from dbo.fnNumbersRC(@test_zero_or_one, @test_n) union all
select count(*) from dbo.fnNumbersRC(@test_zero_or_one, @test_n) union all
select count(*) from dbo.fnNumbersRC(@test_zero_or_one, @test_n) union all
select count(*) from dbo.fnNumbersRC(@test_zero_or_one, @test_n) union all
select count(*) from dbo.fnNumbersRC(@test_zero_or_one, @test_n) union all
select count(*) from dbo.fnNumbersRC(@test_zero_or_one, @test_n) union all
select count(*) from dbo.fnNumbersRC(@test_zero_or_one, @test_n) union all
select count(*) from dbo.fnNumbersRC(@test_zero_or_one, @test_n) union all
select count(*) from dbo.fnNumbersRC(@test_zero_or_one, @test_n) union all
select count(*) from dbo.fnNumbersRC(@test_zero_or_one, @test_n) union all
select count(*) from dbo.fnNumbersRC(@test_zero_or_one, @test_n) union all
select count(*) from dbo.fnNumbersRC(@test_zero_or_one, @test_n) union all
select count(*) from dbo.fnNumbersRC(@test_zero_or_one, @test_n) union all
select count(*) from dbo.fnNumbersRC(@test_zero_or_one, @test_n) union all
select count(*) from dbo.fnNumbersRC(@test_zero_or_one, @test_n) union all
select count(*) from dbo.fnNumbersRC(@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 20x row counts (n=1,000) in  ', inserted.ms_diff, ' milliseconds')  [Performance Summary] 
where test_item='fnNumbersRC';


Performance Summary


fnNumbersRC inserted 20x row counts (n=1,000) in  391 milliseconds


SQL Server parse and compile time: 
   CPU time = 15 ms, elapsed time = 15 ms.
Table '#test_fnNumbersRC_row_counts________________________________________________________________________________________0000000001CE'. Scan count 0, logical reads 20, 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.
Table 'Worktable'. Scan count 40, logical reads 120020, 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 = 296 ms,  elapsed time = 374 ms.

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