Tally Table: Replacing WHILE Loops With a Set of Numbers

Counting one number per loop iteration is work you can express as a set. A tally table provides those numbers once and reuses them across many tasks. GENERATE_SERIES supplies a similar starting point on SQL Server 2022 and later.

Define the Number Range You Need

A tally table contains integers, usually beginning at one or zero, with a unique ordered key. The table is useful because many business tasks can be expressed as positions or offsets. Dates, characters, and missing identifiers are all examples. The numbers are input to those tasks, not business records by themselves.

I define the maximum supported number before building a permanent tally table. A query that needs more positions than the table contains can silently return incomplete results. Capacity is therefore part of the object's contract. A handy table becomes less handy when it stops halfway through the reporting period.

Use a disposable database for this example. The cross-joined VALUES lists generate a known finite range without relying on an unrelated system table's row count. The primary key provides uniqueness and an efficient ordered access path. Build a production utility table only in an approved location with documented ownership.

CREATE TABLE dbo.TallyReviewDemo
(N int NOT NULL PRIMARY KEY CLUSTERED);
WITH Digits AS
(SELECT n FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9))d(n))
INSERT dbo.TallyReviewDemo(N)
SELECT 1+a.n+10*b.n+100*c.n
FROM Digits a CROSS JOIN Digits b CROSS JOIN Digits c;
SELECT MIN(N) AS FirstNumber, MAX(N) AS LastNumber,
       COUNT_BIG(*) AS NumberCount FROM dbo.TallyReviewDemo;

Compare a Date Loop With a Tally Table

A WHILE loop can generate one day per iteration. The following baseline stores its output in a temporary table. It is correct for the stated inclusive endpoints, but it repeats the insert statement for every date. Keep that semantic boundary when replacing it.

DECLARE @Start date='20260101', @End date='20260131', @Day date;
SET @Day=@Start;
CREATE TABLE #LoopDates(DayValue date PRIMARY KEY);
WHILE @Day<=@End
BEGIN
 INSERT #LoopDates VALUES(@Day);
 SET @Day=DATEADD(day,1,@Day);
END;
SELECT DayValue FROM #LoopDates ORDER BY DayValue;
SELECT DATEADD(day,N-1,@Start) AS DayValue
FROM dbo.TallyReviewDemo
WHERE N<=DATEDIFF(day,@Start,@End)+1
ORDER BY N;

The set version converts each number into a day offset. Subtracting one includes the starting date. Adding one to the difference includes the ending date. Review these two choices explicitly because off-by-one mistakes are easy to conceal behind a compact query.

For periods longer than the table's range, either extend the utility table or reject the request before executing it. Also reject an end date before the start date according to your application's input policy. A query returning no rows is not automatically the intended validation response.

Use GENERATE_SERIES When Available

SQL Server 2022 introduced GENERATE_SERIES, which requires database compatibility level 160 or higher. Use it directly when the deployment baseline supports it. The output column is named value. Its type follows the supplied arguments, so use deliberate types for large ranges.

DECLARE @Start date='20260101', @End date='20260131';
SELECT DATEADD(day,value,@Start) AS DayValue
FROM GENERATE_SERIES(0,DATEDIFF(day,@Start,@End),1)
ORDER BY value;

The explicit positive step matters. When the end precedes the start, an omitted step can imply descending generation. Supplying one makes this date-list expression's forward direction clear. Input validation still belongs in the caller's contract.

This function removes maintenance of a physical number table for the task. It does not guarantee that generating a huge range is cheap. The downstream joins, sorts, and result transfer still process the requested rows. Choose a range that matches the real requirement.

Extract Characters With a Tally Table

A loop can call SUBSTRING repeatedly while advancing a position. A tally query exposes every requested position together. The following example uses LEN with a sentinel to preserve trailing spaces in the position count. Ordinary LEN alone excludes trailing spaces.

DECLARE @Text nvarchar(100)=N'ABC  ', @p int=1;
DECLARE @Length int=LEN(@Text+N'#')-1;
CREATE TABLE #LoopCharacters(Position int PRIMARY KEY, CharacterValue nvarchar(2));
WHILE @p<=@Length
BEGIN
 INSERT #LoopCharacters VALUES(@p,SUBSTRING(@Text,@p,1));
 SET @p+=1;
END;
SELECT * FROM #LoopCharacters ORDER BY Position;
SELECT N AS Position, SUBSTRING(@Text,N,1) AS CharacterValue
FROM dbo.TallyReviewDemo WHERE N<=@Length ORDER BY N;

For supplementary Unicode characters, an appropriate supplementary-character collation changes how positions are counted. A displayed character can also consist of multiple combining code points. This example extracts SQL string positions, not every possible user-perceived grapheme. State that limit before using the pattern for user-facing text processing.

Find Missing Identifiers Without a Loop

Missing numeric identifiers are a reporting question, not a reason to refill an identity column. Deleted or rolled-back allocations can legitimately leave gaps. Define the inspected range separately from the table's current maximum so the report has a stable meaning.

CREATE TABLE #ExistingIDs(ID int PRIMARY KEY);
INSERT #ExistingIDs VALUES(1),(2),(4),(7);
DECLARE @ID int=1, @Upper int=8;
CREATE TABLE #LoopMissing(ID int PRIMARY KEY);
WHILE @ID<=@Upper
BEGIN
 IF NOT EXISTS(SELECT 1 FROM #ExistingIDs WHERE ID=@ID)
  INSERT #LoopMissing VALUES(@ID);
 SET @ID+=1;
END;
SELECT ID FROM #LoopMissing ORDER BY ID;
SELECT t.N AS MissingID FROM dbo.TallyReviewDemo t
WHERE t.N<=@Upper
 AND NOT EXISTS(SELECT 1 FROM #ExistingIDs e WHERE e.ID=t.N)
ORDER BY t.N;

The anti-membership query returns candidate numbers with no existing match. It preserves the same range as the loop. Negative identifiers, zero, and noncontiguous business numbering need a different candidate set. Do not assume every integer between two values is a required business record.

Compare Results Before Comparing Time

I verify both directions with EXCEPT when replacing a loop. Matching counts alone can conceal different values. Keep the same ordering where the consumer needs ordered output, and compare NULL behavior and boundary handling.

Use STATISTICS IO and STATISTICS TIME in controlled tests. Small demonstrations are intended to explain the transformation, not publish a dramatic timing claim. Larger representative inputs reveal whether the repeated statements actually matter for the workload. Include result transfer and any staging-table writes in the comparison.

Which part of your loop really needs sequential state? A number generator is a strong candidate for replacement. A business workflow with dependent external actions is a different problem. Rewriting it as a tally query without understanding those dependencies would change behavior rather than improve it.

Keep the Tally Table Predictable

Protect it from accidental duplicate or missing numbers. The clustered primary key enforces uniqueness, while a periodic range check can verify contiguous coverage. Document whether zero is included and which maximum is guaranteed. Those choices affect every offset expression using it.

Keep unrelated attributes out of a general number table. A business calendar deserves separate columns and rules for weekends, holidays, and time zones. Numbers generate dates; they do not decide which dates count as working days. A simple, well-defined source of integers is useful because its contract stays small and testable.

For a permanent utility, compare MIN, MAX, and COUNT under its uniqueness constraint. A complete one-through-maximum range has a count equal to its maximum. Keep writes restricted so ordinary queries cannot remove numbers. This simple invariant is stronger than trusting that the original population script ran successfully years ago. It also catches a partial deployment before missing dates or positions reach a reader.

Related reading on this blog: SQL SERVER 2022: GENERATE_SERIES Function and Removing Repeated Characters From a String With GENERATE_SERIES.

A tally table is not a shortcut around business rules, it is a reusable set of positions that replaces unnecessary counting loops.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.