Mitch Wheat - Treat the cause, not the symptoms!

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
);