You need a Nums or Tally table. You really do. OK, you can make do without, but having one really makes a lot of operations easier.

A Nums table is a good candidate for either an admin database or for the user database, depending on how tightly coupled it is with the stored procedures, functions, views, etc. On the one hand, you might want to make sure that the Nums table moves with a backup and restore of your primary database. On the other hand, you may not want the overhead of multiple Nums tables across several databases.

My preferred way to create and populate a Nums tables is:

create table Nums
(
	n int not null,
	constraint pk_Nums primary key clustered (n)
);
 
with	l0 as (select 1 v union all select 1),
		l1 as (select a.v from l0 a, l0),
		l2 as (select a.v from l1 a, l1),
		l3 as (select a.v from l2 a, l2),
		l4 as (select a.v from l3 a, l3),
		l5 as (select a.v from l4 a, l4),
		n as (select row_number() over (order by (select null)) n from l5)
insert Nums(n)
select n.n
from n
where n.n <= 1000000;

The downside here is that the code is ugly, and unless you are familiar with what’s
going on it may take a bit to decipher the script.

The upside is that the script is fast (about 5 seconds on my machine to load 1,000,000 records) and is quite adaptable. If you want more than one million numbers in the Nums table, just increase the value in the script up to about 4 billion. And if for reason you need more than that just add another CTE for “l6” following the same pattern.

For a million numbers, this occupies less than 13 MB of storage, so in most cases the overhead for a single database is pretty trivial.

Finally, I really like the flexibility of taking this same method and generating a virtual Nums table. Yes, it add a lot of ugly to an otherwise nice script, but you also get independence from having to have that Nums table, which is useful in conditions where can’t have an Admin database and can’t modify the user database.

If I want to find gaps in the IDs in my Customer table, I can come up with something like this:

declare @maxCustomerId int = (select max(CustomerID) from Customer);
 
with	l0 as (select 1 v union all select 1),
		l1 as (select a.v from l0 a, l0),
		l2 as (select a.v from l1 a, l1),
		l3 as (select a.v from l2 a, l2),
		l4 as (select a.v from l3 a, l3),
		l5 as (select a.v from l4 a, l4),
		Nums as (select row_number() over (order by (select null)) n from l5)
select	Nums.n MissingCustomerId
from	Nums left join Customer cust on Nums.n = cust.CustomerID
where	Nums.n <= @maxCustomerId
and		cust.CustomerID is null;

The nice thing is that SQL Server quite efficiently optimizes the query and doesn’t evaluate any more CTEs than are really necessary based on the constraints used on the Nums CTE.

This also makes it easy and efficient to create a table-valued function instead of (or in addition to) a persisted Nums table:

if exists (select * from sys.objects where type = 'IF' and name = 'fnNums')
	drop function dbo.fnNums;
go
create function dbo.fnNums()
returns table
as
return
with	l0 as (select 1 v union all select 1),
		l1 as (select a.v from l0 a, l0),
		l2 as (select a.v from l1 a, l1),
		l3 as (select a.v from l2 a, l2),
		l4 as (select a.v from l3 a, l3),
		l5 as (select a.v from l4 a, l4),
		Nums as (select row_number() over (order by (select null)) n from l5)
select Nums.n
from Nums
go