The calendar report has blank days, and the data has no rows for those days. A numbers table supplies the missing rows. GENERATE_SERIES supplies them without a permanent object when SQL Server 2022 and compatibility level 160 are available.
Store a Small Numbers Table Backbone
I keep a numbers table when the same database needs ranges in several places. Give the integer column a clustered primary key and store consecutive values. The sample creates a finite demonstration set, not a claim about your workload. Increase its supported range deliberately. A clustered key prevents duplicates and gives the optimizer statistics on a real stored column. Check the minimum and maximum before using it for dates. A table with a mysterious gap is a very quiet bug. Which upper bound does your application actually promise to support? Document that bound beside the table definition. The first block’s final query returns 1 and 1000, which is the whole promise of this sample.
CREATE TABLE dbo.Numbers (n int NOT NULL PRIMARY KEY CLUSTERED);
WITH d(n) AS (SELECT n FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS v(n))
INSERT dbo.Numbers(n)
SELECT 1+a.n+10*b.n+100*c.n FROM d AS a CROSS JOIN d AS b CROSS JOIN d AS c;
SELECT MIN(n) AS FirstNumber, MAX(n) AS LastNumber FROM dbo.Numbers;Generate the Same Range Explicitly
SQL Server 2022 introduces GENERATE_SERIES. The database must use compatibility level 160 or higher. Check that setting before copying the second query. Both sources return integers here, but the generated function also accepts supported numeric types. Match types across the join so an implicit conversion does not spoil the comparison. I specify the step even when the default would work. That makes intent clear when the endpoints change. Do not raise a production database compatibility level just to try a demonstration. Use a database already configured for the feature, or keep the persisted source on older versions.
SELECT name, compatibility_level FROM sys.databases WHERE database_id=DB_ID();
SELECT value FROM GENERATE_SERIES(1,1000,1);Build Missing Dates From the Numbers Table
Date gap filling starts with the complete date set, then left joins the facts. Put filters on the fact table in the join when empty days must remain. A WHERE predicate against the nullable side can remove the very gaps you intended to show. The example only builds the date backbone so you can attach your own daily aggregate. Subtract one from the stored positive integer because the first date uses offset zero. Use an exclusive upper date in the fact query when its column includes a time. Midnight boundaries deserve an explicit rule, not a hopeful conversion.
DECLARE @start date='20260101', @finish date='20260107';
SELECT DATEADD(day,n-1,@start) AS CalendarDate
FROM dbo.Numbers WHERE n BETWEEN 1 AND DATEDIFF(day,@start,@finish)+1;
SELECT DATEADD(day,value,@start) AS CalendarDate
FROM GENERATE_SERIES(0,DATEDIFF(day,@start,@finish),1);
Test Both Row Sources at the Edges
Compare the two forms against the same input, in the same database. Test an empty range, a single day, and the largest range you promise to support. GENERATE_SERIES(5,1) with no step counts down from 5 to 1, because the default step follows the direction of the endpoints. GENERATE_SERIES(1,5,-1) returns no rows at all. Pick an explicit rule for a backward range instead of trusting whichever default you get. The stored table has a different edge: it stops at its largest value. A request past that value quietly returns fewer rows. Enable the actual execution plan in SSMS, and read the rows entering and leaving each join. Save the plan and the Messages output together, so the comparison can be repeated later. The calendar block makes a good fixture. Both of its queries return the same seven dates, January 1 through January 7, 2026. The chunk block returns ten ranges, from 0 to 100 up to 900 to 1000, each with an exclusive end. Save those outputs, then rerun both forms after any change to the stored table or the compatibility level. A different row count is the first sign that an endpoint rule changed.
Turn Integers Into Work Ranges
The same set divides a key space into work chunks. Calculate each start from the chunk number, then calculate its exclusive end. Use bigint arithmetic before multiplication when the overall key range is large. Avoid overlapping inclusive endpoints. A numbers table gives a reusable backbone for this work, while the generated form follows the requested endpoints. Neither source guarantees that every chunk contains equal work. Sparse keys and skewed data change the load. Persist completed chunk boundaries when the process must resume after failure. Generating the list again does not prove that its earlier chunks finished.
DECLARE @width bigint=100;
SELECT CONVERT(bigint,n-1)*@width AS StartKey,
CONVERT(bigint,n)*@width AS EndKeyExclusive
FROM dbo.Numbers WHERE n<=10 ORDER BY n;Read the Estimate at the Join
Enable SET STATISTICS IO ON and inspect the actual plan for each date or chunk join. Read the estimated rows on the row source, then compare the actual rows entering the join. Stored statistics and a generated range give the optimizer different information. Parameterized endpoints also change what the optimizer knows at compilation. Do not promise that one source always estimates better. A poor estimate affects join choice and memory grants farther downstream. The useful comparison is the complete joined query, using the same filters and inputs. Turn statistics output off after capturing the evidence you need.
Keep the Numbers Table Honest in Production
Give the revised statement the same correctness checks as the original. Compare values, duplicates, and behavior when nothing matches. Keep the stored table large enough for the maximum supported request, and check that request before the query runs. A simple guard compares the requested count with MAX(n) and raises an error when it is too large. GENERATE_SERIES has no such ceiling, but every database that runs the code needs compatibility level 160 or higher. At level 150 the same query fails with an invalid object name error. So a script that moves between servers is safer with the stored table, while new code on a current database can use the function. Pick one form per code path and write down why. That note tells the next DBA which rule still applies.
Related reading on this blog: SQL SERVER 2022: GENERATE_SERIES Function and Generate A Single Random Number for Range of Rows of Any Table: Very interesting Question from Reader.
A row generator is not a shortcut around estimates, it is an input to the plan.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
