For anyone who's worked with SQL Server for a long time, you'll know that one of the common requests is that a table of numbers would be useful. Yes, just a table with 1, 2, 3, 4 and so on. Often in a query, you need just a certain set of values.
In our free SDU Tools, we've added a function to do that in a reasonably efficient way.
In the main screenshot, you can see it in use. We made it a little more flexible in that you can specify the starting number, and then specify the number of numbers.
Because we've done this with a recursive function, if you need to output more than 100 values, it's important to add a MAXRECURSION option into the query like this:
We hope you find it useful. You can see it in action here:
For more information on joining our insiders team to get all our free tools and resources, follow the link here:
Hi Greg,
Please see the following for why you shouldn't use an rCTE for a "numbers" function. It's not at all "fairly efficient".
https://www.sqlservercentral.com/articles/hidden-rbar-counting-with-recursive-ctes
Hi Jeff, agreed. We actually changed to a different method a while back. And for multiple reasons. One was that we don't want to have a need for an OPTION(MAXRECURSION) anyway.