A numbers table is simply a table containing sequential integer values. The size of the required table will vary depending upon its use, but it is not uncommon to find permanent tables containing 1,000,000 rows. A numbers (or tally) table forms the basis of solving problems in a set-based way, and is a powerful technique for finding gaps in sequences, string manipulation (which if done purely with TSQL’s built-in functions can run very slowly), etc. It is also often used in the form of a date table, enabling some very efficient set based join constructs to be used on date range calculations.
While it is usual to create a fixed table in the database, there is the occasional need for a dynamic version. There are several ways not to do this! Such as the recursive CTE or a while loop inserting values one after another. A better option is the CROSS join against system tables method, and better still is Itzik’s often cited, cross joined CTE method:
--===== Itzik's CROSS JOINED CTE method
WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),
E02(N) AS (SELECT 1 FROM E00 a, E00 b),
E04(N) AS (SELECT 1 FROM E02 a, E02 b),
E08(N) AS (SELECT 1 FROM E04 a, E04 b),
E16(N) AS (SELECT 1 FROM E08 a, E08 b),
E32(N) AS (SELECT 1 FROM E16 a, E16 b),
cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E32)
SELECT N
FROM cteTally
WHERE N <= 1000000;
GO
After a little experimentation, I found that reducing the number of cross joins by increasing the size of the first table results in a more efficient query plan. I wrapped this up in an inline table valued function with the option of a start and end value for the sequence:
IF OBJECT_ID (N'dbo.tvf_DynamicNumbersTable') IS NOT NULL
DROP FUNCTION dbo.tvf_DynamicNumbersTable
GO
CREATE function dbo.tvf_DynamicNumbersTable
(
@pStartValue int = 1,
@pEndValue int = 1000000
)
returns table
as
return
(
with rows16(N) as
(
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1
),
rows256(N) as
(
select t1.N from rows16 t1 cross join rows16 t2
),
rows65536(N) as
(
select t1.N from rows256 t1 cross join rows256 t2
),
rows2pow32(N) as
(
select t1.N from rows65536 t1 cross join rows65536 t2
),
Numbers(N) as
(
select row_number() over (order by N) as N from rows2pow32 t1
)
select N from Numbers
where N between @pStartValue AND @pEndValue
);