Performance test of generating a set of sequential numbers

I have read so much lately about how bad it is to use a recursive CTE to do… well pretty much anything. It came up in a discussion about creating sequential number, and not in a positive sort of way.

I wrote about recursive CTEs when they were first added to SQL Server, and have been a fan for doing breadth-first searching of a hierarchy/graph, but never even thought to use one to generate a set of numbers. As I kept hearing how bad it this method was, so I figured, let’s see just how terrible it is. And of course, I needed something to compare to, so I decided to try all of the ways I could think of. So I will generate varying numbers of rows with the following methods:

Recursive CTE – A SQL construct that will basically query itself for multiple iterations.
Good old fashioned WHILE loop – Just a while loop adding one for each iteration
A cool mathy type generator using CROSS JOIN operators – Something I have used before, that generates digits with cross joins of varying mathematical equations.
Using ROW_NUMBER() on a very large set of rows with CROSS JOIN – Basically, generate a lot of rows that start returning data quickly, then use the ROW_NUMBER function on the rows.
GENERATE_SERIES – The built-in function in SQL Server 2022 and beyond that simply solved the problem in a very nice way indeed.

I will present the code in each section, and then I will execute these functions, providing a listing of the time it took to do 10, 100, 1000, 10000, 100000, and 100000 rows for each method.

Which will be faster? Read on.

The methods

In these sections, I will briefly describe the methods I will be using in my tests.

Recursive CTE

I had never tried this, but honestly, it is a really simple method. The basics are that you start with a starting value (which I will always start with 1 for these tests), and for each recursion, it will add one to the value and add it to a result set.

			
WITH BaseRows AS (
      SELECT 1 AS value
      UNION ALL
      SELECT value + 1
      FROM   BaseRows
      WHERE  value BETWEEN 1 AND 9
)
SELECT Value
FROM   BaseRows
OPTION (MAXRECURSION 0); --this makes the boundary data type based.
                         --so over 2 billion for the int type that
                         --that SELECT 1 as value would use.

		

All of the outputs for the code explanations will look like this:

			
-------------
1
2
3
4
5
6
7
8
9
10

		

For the performance tests, I will send the results to a temp table.

Good old-fashioned WHILE loop

This is the first algorithm we all used at one time. Just a loop and a counter. Not ideal with SQL, so we learned to create a table of numbers (also known as a sequence table or tally table).

			
CREATE TABLE #HoldValues (value int);
DECLARE @counter INT = 1
WHILE @counter &lt;= 10
BEGIN
      INSERT INTO #holdValues(value)
      SELECT @counter
      
      SET @counter = @counter + 1
END
SELECT * FROM #HoldValues;

		

### Using ROW_NUMBER() on a very large set of rows

I never personally used this method, but it is something that people do. For this, you find a table that has a good number of rows, typically a system view, like:

			
SELECT COUNT(*)
FROM   master.sys.indexes;

Then use:

			
SELECT COUNT(*)
FROM   master.sys.indexes
         CROSS JOIN  master.sys.indexes as i2
         CROSS JOIN  master.sys.indexes as i3;

The output for this is: 10503459, which is good enough for our use, where I want to do at least 1 million values in the sequence. Then just do something like:

			
SELECT TOP 10 ROW_NUMBER() OVER (ORDER BY i2.object_id) as value
FROM   master.sys.indexes
         CROSS JOIN  master.sys.indexes as i2
         CROSS JOIN  master.sys.indexes as i3;

This is something I saw more of before the advent of GENERATE_SERIES because it is very quick to write and works for even moderately large sets. Most of the time we don’t care about performance too much because we are loading a table with values. But when working ad hoc, sometimes you just don’t want to wait. Luckily, too, because there is no row filtering, it can start spooling results very fast.

This method and the next are really good, but you need to have control over the max number of rows you want to put out, and what the min and max are. They generally expect you start at 1 or zero, and if you want something else, you have to change the value with an offset. That offset won’t change the performance of the code, so not a big deal.

A cool mathy type generator

This code is something that someone smarter than me came up with. I have used it many times, and I understand how to use it, but it is more math than I like to think about 🙂

			
;WITH digits (I) AS (--set up a set of numbers from 0-9
      SELECT I
      FROM   (VALUES (0),(1),(2),(3),(4),(5),
                  (6),(7),(8),(9)) AS digits (I))
      --builds a set of data from from 0 to 999999
      ,Integers (I) AS (
            SELECT D1.I + (10<em>D2.I) + (100</em>D3.I) +
                  (1000<em>D4.I) + (10000</em>D5.I) + (100000<em>D6.I) +
                  (1000000</em>D7.I)
            FROM digits AS D1 CROSS JOIN digits AS D2
                  CROSS JOIN digits AS D3
                  CROSS JOIN digits AS D4
                  CROSS JOIN digits AS D5
                  CROSS JOIN digits AS D6
                  CROSS JOIN digits AS D7 )
--insert into table
SELECT I + 1 --starts with 0
FROM   Integers
WHERE  I + 1 &lt;= 10;

		

The problem with this method is basically that you have to choose a size if you are writing it into code. And at these larger sizes, this can be pretty CPU-intensive. (My fans start cranking the second I run, even this 1-10 query because of it.

GENERATE_SERIES

And finally, the newest of them all, the built-in functionality. Which is fastest? That is coming up next.

			
SELECT value
FROM   GENERATE_SERIES(1,10);

This can’t be the fastest, can it? Generally speaking, the built-in tools aren’t always the best performers… but maybe this time?

The results

Using the code in the Appendix that follows this section, the tests were run on my test machine that you can see listed here as my Test Machine (NUC9i7QNX). I used SQL Server 2025 Enterprise RTM for my tests.

In these tests, here is the thing. None of these methods performed so poorly that loading a table of numbers with them is particularly terrible. All of them produced a set of a million rows in less than 12 seconds. But the absolute best performer turned out to be:

			
SectionName \ Rows    10    100    1000   10000  100000  1000000 Rows
--------------------- ----- ------ ------ ------ ------- ------------
Recursive CTE         1     3      12     120    929     9292
While Loop            1     2      13     112    1116    11661
CROSS JOIN ROWNUMBER  59    63     95     97     397     3602
CROSS JOIN Digits     5550  5505   5573   5423   5615    5952
GENERATE_SERIES       2     1      2      12     103     1123
Times in milliseconds.

		

GENERATE_SERIES! By quite some distance. Interesting to me was that the CROSS JOIN ROWNUMBER method was the second-best performer. I had expected that one to be much slower, honestly.

In any case, if you have a need to generate a series of integers, use GENERATE_SERIES. It is the fastest available method in SQL Server and is just plain easier. You can tailor your output to be what you need with parameters for start, stop, and a step value that can be int, bigint, or even numeric.

One thing to note, the only method that uses parallelism is the CROSS JOIN Digits method. And in order to create the million rows I want and use the same code, it cranked my machine hard. However, while it was over 5 seconds to generate 10 rows, it was only 5 seconds to generate a million rows. If I weren’t afraid my computer was going to melt into a puddle of silicone, I would see just how far I could go with it.

Appendix – The test code

This should work as is on any machine. It is set to create a table in your database that will hold the output and the times. It is also set to reset when you do 10 rows, so don’t forget that and lose your samples.. not that I did that or anything.

			
--Note: code isn't well formatted. 
SET NOCOUNT ON;
IF OBJECT_ID('dbo.HoldTimes') is null
BEGIN
CREATE TABLE dbo.HoldTimes (
sectionName varchar(30),
starttime datetime2,
endtime datetime2,
sampleSize int,
DurationMilliseconds as (DATEDIFF(millisecond,starttime, endtime))
);
END;
DROP TABLE IF EXISTS #holdValues;
CREATE TABLE #holdValues (value INT NOT NULL PRIMARY KEY (value));
DECLARE @maxValue INT = 100000 --0000;
IF @maxValue = 10
TRUNCATE TABLE dbo.HoldTimes
<hr />
PRINT 'Recursive CTE';
TRUNCATE TABLE #holdValues;
INSERT INTO dbo.HoldTimes (SectionName,StartTime,SampleSize)
VALUES ('Recursive CTE',SYSDATETIME(),@maxValue);
WITH BaseRows AS (
SELECT 1 AS value
UNION ALL
SELECT value + 1
FROM   BaseRows
WHERE  value BETWEEN 1 AND (@MaxValue - 1)
)
INSERT INTO #holdValues (value)
SELECT Value
FROM   BaseRows
OPTION (MAXRECURSION 0);
UPDATE dbo.HoldTimes
SET    EndTime = sysdatetime()
WHERE  sectionName = 'Recursive CTE'
AND  sampleSize = @maxValue
IF (SELECT COUNT(*) from #holdValues) &lt;&gt; @MaxValue
THROW 50000,'ERROR Occurred',1;
<hr />
PRINT 'While Loop';
TRUNCATE TABLE #holdValues;
INSERT INTO dbo.HoldTimes (SectionName,StartTime,SampleSize)
VALUES ('While Loop',SYSDATETIME(),@maxValue);
DECLARE @counter INT = 1
WHILE @counter &lt;= @maxvalue
BEGIN
INSERT INTO #holdValues(value)
SELECT @counter
<pre><code>SET @counter = @counter + 1</code></pre>
END
UPDATE dbo.HoldTimes
SET    EndTime = sysdatetime()
WHERE  sectionName = 'While Loop'
and  sampleSize = @maxValue
IF (SELECT COUNT(*) from #holdValues) &lt;&gt; @MaxValue
THROW 50000,'ERROR Occurred',1;
<hr />
PRINT 'CROSS JOIN ROWNUMBER';
TRUNCATE TABLE #holdValues
INSERT INTO dbo.HoldTimes (SectionName,StartTime,SampleSize)
VALUES ('CROSS JOIN ROWNUMBER',SYSDATETIME(),@maxValue);
INSERT INTO #holdValues(value)
SELECT TOP (@MaxValue) ROW_NUMBER() OVER (ORDER BY i2.object_id) as value
FROM   master.sys.indexes
CROSS JOIN  master.sys.indexes as i2
CROSS JOIN  master.sys.indexes as i3;
UPDATE dbo.HoldTimes
SET    EndTime = sysdatetime()
WHERE  sectionName = 'CROSS JOIN ROWNUMBER'
and  sampleSize = @maxValue
IF (SELECT COUNT(*) from #holdValues) &lt;&gt; @MaxValue
THROW 50000,'ERROR Occurred',1;
<hr />
PRINT 'CROSS JOIN Digits';
TRUNCATE TABLE #holdValues
INSERT INTO dbo.HoldTimes (SectionName,StartTime,SampleSize)
VALUES ('CROSS JOIN Digits',SYSDATETIME(),@maxValue);
;WITH digits (I) AS (--set up a set of numbers from 0-9
SELECT I
FROM   (VALUES (0),(1),(2),(3),(4),(5),
(6),(7),(8),(9)) AS digits (I))
--builds a set of data from from 0 to 999999
,Integers (I) AS (
SELECT D1.I + (10<em>D2.I) + (100</em>D3.I) +
(1000<em>D4.I) + (10000</em>D5.I) + (100000<em>D6.I) +
(1000000</em>D7.I) + (10000000*D8.I)
FROM digits AS D1 CROSS JOIN digits AS D2
CROSS JOIN digits AS D3
CROSS JOIN digits AS D4
CROSS JOIN digits AS D5
CROSS JOIN digits AS D6
CROSS JOIN digits AS D7
CROSS JOIN digits AS D8 )
--insert into table
INSERT INTO #holdValues (value)
SELECT I + 1
FROM   Integers
WHERE  I &lt;= @MaxValue - 1;
UPDATE dbo.HoldTimes
SET    EndTime = SYSDATETIME()
WHERE  sectionName = 'CROSS JOIN Digits'
AND  sampleSize = @maxValue
IF (SELECT COUNT(*) FROM #holdValues) &lt;&gt; @MaxValue
THROW 50000,'ERROR Occurred',1;
<hr />
PRINT 'GENERATE_SERIES'
TRUNCATE TABLE #holdValues;
INSERT INTO dbo.HoldTimes (SectionName,StartTime,SampleSize)
VALUES ('GENERATE_SERIES',SYSDATETIME(),@maxValue);
INSERT INTO #holdValues (value)
SELECT value
FROM   GENERATE_SERIES(1,@MaxValue)
UPDATE dbo.HoldTimes
SET    EndTime = SYSDATETIME()
WHERE  sectionName = 'GENERATE_SERIES'
AND  sampleSize = @maxValue;
IF (SELECT COUNT(*) FROM #holdValues) &lt;&gt; @MaxValue
THROW 50000,'ERROR Occurred',1;
<hr />
PRINT 'That'' All'
SELECT SectionName,
MAX(CASE WHEN SampleSize = 10 THEN DurationMilliseconds ELSE -1 END) AS [10 Rows],
MAX(CASE WHEN SampleSize = 100 THEN DurationMilliseconds ELSE -1 END) AS [100 Rows],
MAX(CASE WHEN SampleSize = 1000 THEN DurationMilliseconds ELSE -1 END) AS [1000 Rows],
MAX(CASE WHEN SampleSize = 10000 THEN DurationMilliseconds ELSE -1 END) AS [10000 Rows],
MAX(CASE WHEN SampleSize = 100000 THEN DurationMilliseconds ELSE -1 END) AS [100000 Rows],
MAX(CASE WHEN SampleSize = 1000000 THEN DurationMilliseconds ELSE -1 END) AS [1000000 Rows]
FROM   dbo.HoldTimes
GROUP  BY SectionName

		

5 responses to “Performance test of generating a set of sequential numbers”

Performance Testing Sequential Number Generation – Curated SQL

2026-01-29

[…] Louis Davidson breaks out the stopwatch: […]

Loading…

This post is not only about SELECT * – Drsql.link

2026-02-02

[…] goes for everything you teach. Start with the most practical reasons. In my post last week on generating a sequence of numbers (which has a follow-up coming!), I tested using what I had heard was a terrible method for generating such numbers, […]

Loading…

Generating a set of sequential numbers, part 2 – Drsql.link

2026-02-04

[…] I wrote the blog from last week (Performance test of generating a set of sequential numbers), I was mostly wanting to see how well I could do this using a recursive CTE. I wondered how […]

Loading…

A SQL method FOR computing rolling totals without window functions – Drsql's Database Musings

2026-02-18

[…] to include those ranges using a CTE that uses GENERATE_SERIES. You can use any of the techniques in this blog if you don’t have that function […]

Loading…

Generating a set of sequential numbers, part 2 – Drsql's Database Musings

2026-02-26

[…] I wrote the blog from last week (Performance test of generating a set of sequential numbers), I was mostly wanting to see how well I could do this using a recursive CTE. I wondered how […]

Loading…

Drsql's Database Musings