SQL Server Numbers table

Posted by Joe Enos on August 18, 2021 · 3 mins read
if not exists (select 1 from information_schema.schemata where schema_name = 'util')
begin
exec sp_executesql N'create schema [util];';
end;
go

create table util.Numbers (
Num int not null
,constraint PK_util_Numbers primary key (Num)
);
go

declare @MaxValue int = 1000000;
while (select count(1) from util.Numbers) < @MaxValue
begin
declare @n int = @MaxValue - (select count(1) from util.Numbers);
declare @offset int = isnull((select max(Num) from util.Numbers), 0);
insert util.Numbers
select top (@n) row_number() over (order by o1.[object_id]) + @offset
from sys.objects o1
cross join sys.objects o2
end;

select * from util.Numbers order by Num;
go