Any time I've needed to populate a table of sequential numbers, I just done it by brute force. No recursion or fancy stuff. Remember the definition of place valued...

Here's a set of comparisons between fnNumbers and fnTally

Performance comparison of fnNumbers vs fnTally inserting 10x row counts (n=1,000)

Performance comparison of fnNumbers vs fnTally inserting 10x row counts...

Here are new Fiddles.  The old Fiddles were not in this more popular Fiddle.  There's more than one Fiddle FYI.

Performance comparison of fnNumbers vs fnNumbersRC inserting 10x row counts...

If you look at my answer count v.s. the number of points I have and the position I have on Stack Overflow, you'll see that quantity doesn't matter much...

Steve, You've got a wrong analogy here.

The better one would be this:;

You live in late Soviet Union and have to choose which car to buy: Porsche 911 and ZAZ...

The WHILE loopers always think everyone else is/are crazy.  That person's query also...

Ah... almost forgot... What to do about the folks that claim that performance doesn't matter if you know that here will always only be a small number of rows...

Ok here are two recent winning recursive cte's.  Be gentle when evaluating the opposing answers 🙂

https://stackoverflow.com/questions/63922869/get-an-interval-of-dates-from-a-range-of-dates/63923536

https://stackoverflow.com/questions/63919706/fill-missing-months-on-a-date-query/63920844

As a bonus, here losing while using an ordinal splitter

https://stackoverflow.com/questions/63886788/extracting-date-from-text-in-sql-using-cursor/63887736

Here's a DB Fiddle that shows fnNumbers is about 4-10x faster to generate 100,000 rows.

Here's a DB Fiddle that shows fnNumbers is about 2x faster to generate 10,000 rows.

Here's...

It should be said I'm not looking for an advantage not available to anyone else.  As soon as a different method works the others will just switch and then it's...

Jeffrey Williams, the more I look at your code the more I like it.  I definitely missed a few things and using TOP in the select is so obvious now. ...

If the number of queries is increased to 100 then ms_diff of the tally table is only 16 whereas for the recursive CTE it's 2531!

Ok, here's a different comparison.  Suppose the query contains 34 UNION ALL SELECTS from a tvf generating 1000 row values in sequence.

declare
  @start_dt   ...

That's over 100,000 rows.  When it drops to 10,000 the difference is much less.  Then when it drops to 1,000 the difference is so small it's hard to measure.

The first part of the problem is how you are generating the tally - you are generating too many rows.  The second part of the problem is the return...