Auxiliary table of numbers

There are all kinds of ways that an auxiliary table of numbers can be useful. Just today I used one to generate a bunch of test data.

If you want your own table of nums, try this:

create table dbo.nums (num int not null primary key);
insert into dbo.nums (num) select 1;
insert into dbo.nums (num) select 2;
go

create procedure dbo.topup(@max int) as
begin
 if ((select count(*) from nums) < @max)
 begin
  while ((select count(*) from nums) < @max/2)
  begin
   insert into dbo.nums (num) select num + count(*) over() from nums;
  end;

  insert into dbo.nums (num)
  select n
  from (select num + count(*) over() as n from nums) t
  where n <= @max;
 end;
end;

This will start your table off very small, but you can call the topup procedure every time you need it. For example, if you know you're going to need to have to it go as high as the number of rows in the blah table, do:

declare @newmax int
select @newmax = count(*) from blah
exec dbo.topup @newmax

--before your query that uses nums.