The Problem of Missing Integer Sets

When I need to build a calendar dimension for a reporting warehouse, I often find myself wanting a continuous sequence of integers. In the past I kept a static 'Numbers' table that I had to maintain as the business grew. It worked, but it added overhead every time a new range was required.

A cleaner solution lives right inside the query engine: a recursive common table expression (CTE) that can spin up a temporary numbers table on the fly. It’s a pattern I rely on daily for tasks ranging from date‑series generation to filling gaps in surrogate‑key lookups. Below I’ll walk you through the technique, why it matters, and how to put it into production‑grade code.

Why a Recursive CTE Beats a Hand‑Coded List

  • Zero‑maintenance. No separate objects to migrate or rebuild when you need more rows.
  • Set‑based simplicity. You can reference the CTE once and reuse it across multiple statements.
  • Portable. The same pattern works in SQL Server, PostgreSQL, Oracle, and even MySQL (with slight syntax tweaks).
  • Composable. Combine it with other CTEs or derived tables without worrying about lock contention on a persisted table.

The trade‑off is pure performance for very large ranges—recursive CTEs are not as fast as a permanent, indexed table. If you anticipate needing millions of rows on a regular basis, materializing the numbers into a small lookup table is a better choice. For typical reporting windows (a few thousand rows) the recursive approach is fast enough and far simpler.

Real‑World Scenario: Building a Date Dimension

Imagine you are loading a data warehouse that requires a complete date dimension for every day in the last decade. The classic pattern is:

WITH N(N) AS
(
    SELECT 0 UNION ALL SELECT N+1 FROM N WHERE N < 3650
)
SELECT DATEADD(day, N, CAST('2000-01-01' AS date)) AS CalendarDate
FROM N;

The CTE `N` generates integers from 0 up to 3,650 (≈10 years). The outer query then shifts a base date by each integer, producing a full calendar. You can extend the result set with attributes like year, quarter, month, weekday, and holiday flags—all derived in set‑based fashion.

Tip: If you need a larger range, break the recursion into two CTEs (e.g., generate 0‑999 and 1000‑9999) to stay within the default recursion limit of 100 levels. This keeps the plan simple while scaling to millions of rows.

Another common use‑case is filling missing surrogate keys in a lookup table. Suppose you have an `Orders` table that lost a few IDs after a data purge. You can generate a补全 set with the recursive CTE and `MERGE` it back into the dimension without manual intervention.

Writing Production‑Ready Code

When I drop this pattern into production, I add a few defensive measures:

  1. Limit the recursion depth to avoid accidental infinite loops.
  2. Use an explicit CAST to the target data type (int, decimal, etc.).
  3. Wrap the CTE in a parameterized procedure if the range is variable.

Here’s a complete, commented example for SQL Server that you can drop into a stored procedure and reuse:

-- ==========================================================
-- Generate a sequential list of integers for ad‑hoc reporting.
-- Usage:  SELECT * FROM dbo.GenerateNumbers(0, 9999);
-- ==========================================================
CREATE OR ALTER PROCEDURE dbo.GenerateNumbers
    @MinValue   INT = 0,
    @MaxValue   INT = 9999
AS
BEGIN
    SET NOCOUNT ON;

    /*
       Recursive CTE that builds a set of integers from @MinValue
       up to @MaxValue. The anchor selects the starting value, and the
       recursive member adds 1 each iteration. The WHERE clause stops
       recursion before we exceed the requested maximum.
    */
    WITH N(N) AS
    (
        SELECT @MinValue UNION ALL
        SELECT N + 1
        FROM N
        WHERE N < @MaxValue
    )
    SELECT N AS SequenceNumber
    FROM N
    OPTION (MAXRECURSION 0);   -- 0 = unlimited (respects server limit)
END;
GO

/* Example usage – produce a calendar for the year 2023 */
SELECT  cal.DateValue,
        cal.Year,
        cal.Quarter,
        cal.Month,
        cal.DayOfWeek,
        cal.DayOfMonth
FROM    dbo.GenerateNumbers(0, 365) AS n
CROSS APPLY
(
    SELECT DATEADD(day, n.SequenceNumber, CAST('2023-01-01' AS date)) AS DateValue
) AS d
CROSS APPLY
(
    SELECT
        YEAR(d.DateValue)               AS Year,
        DATEPART(quarter, d.DateValue)  AS Quarter,
        DATEPART(month, d.DateValue)    AS Month,
        DATEPART(weekday, d.DateValue)  AS DayOfWeek,
        DATEPART(day, d.DateValue)      AS DayOfMonth
) AS cal
ORDER BY cal.DateValue;

The procedure `dbo.GenerateNumbers` is now a reusable component. You can call it from any script that needs a sequence, and you never have to worry about keeping a static lookup table in sync.

When to Materialize Instead

If your reporting windows stretch into the millions of rows, the recursive CTE starts to show CPU cost. In those cases I spin up a tiny permanent table called `dbo.Numbers` (populated once using the same pattern) and add a clustered index on the sequence column. The permanent version is then referenced wherever a large integer set is needed.

The decision boils down to a simple rule of thumb:

  • ≤ 50,000 rows – keep it inline with a recursive CTE.
  • > 50,000 rows – materialize into a lookup table for performance.

Takeaway

Recursive CTEs give you a fast, maintenance‑free way to generate integer series on demand. Whether you are building a date dimension, filling surrogate‑key gaps, or simply need a sequence for testing, the pattern is concise, readable, and production‑ready when you add a few guardrails. Try it in your next ETL job or reporting sprint—you’ll likely find it becomes a go‑to trick just like I do.