Data row generator (good performance, w/o recursive CTE), Sql Server

Data row generator (good performance, w/o recursive CTE)

if object_id('dbo.fn_rowgen', 'IF') is not null
	drop function dbo.fn_rowgen;
go
create /*or alter*/ function dbo.fn_rowgen(@n int)
returns table
as
return (
	with base as (
		select slot
		from (
			values
			(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
			(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
			(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
			(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
			(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
			(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
			(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
			(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
			(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
			(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
			(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
			(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
			(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
			(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
			(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
			(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
		) base(slot)
	), rows as (
		select top(@n) 0 as dummy
		from (
			select 0 as dummy
			from base b0
				inner hash join base b1 on b1.slot + b0.slot = 0
		) b0 inner loop join (
			select 0 as dummy
			from base b0
				inner hash join base b1 on b1.slot + b0.slot = 0
		) b1 on b1.dummy = b0.dummy
	)
	select row_number() over (order by dummy) as [%%rownum%%]
	from rows
)
go

if object_id('tempdb..#tmp', 'U') is not null
	drop table /*if exists*/ #tmp;
go
set statistics time on;
go
select * into #tmp from dbo.fn_rowgen(10000000) /*option (maxdop 1)*/ /*option (querytraceon 8649)*/;
go
set statistics time off;
go


run \| edit \| history \| help	0