Generating A Series Of Numbers In SQL Server

One of the more powerful features of LINQ is the ability to generate sequences of values.

To get a list of numbers from 1 to 10, we do it like so:

var numbers = Enumerable.Range(1, 10).ToList();
numbers.ForEach(Console.WriteLine);

To get a list of all even numbers from 1 to 10, we do it like this:

var evenNumbers = Enumerable.Range(1, 10).Where(x => x % 2 == 0).ToList();
evenNumbers.ForEach(Console.WriteLine);

To get a list of all odd numbers from 1 to 10, we do it like this:

var oddNumbers = Enumerable.Range(1, 10).Where(x => x % 2 != 0).ToList();
oddNumbers.ForEach(Console.WriteLine);

You might wonder if it was possible to generate smaller increments, like 0.0 to 1.0 in steps of .05.

It is.

var smallNumbers = Enumerable.Range(0, 20).Select(x => x / 20.0).ToList();
smallNumbers.ForEach(Console.WriteLine);

Do you wish there were something similar in Microsoft SQL Server?

There is!

You can use the generate_series function for such purposes. This has been available from SQL Server 2022.

It takes three parameters:

1. Start
2. Stop
3. An optional step, which defaults to 1

The scenarios above would this be as follows:

-- 1 to 10
select value
from generate_series(1, 10)
-- odd numbers
select value
from generate_series(0, 10, 2)
-- even numbers
select value
from generate_series(1, 10, 2)
-- 0.0 to 1.0 in steps of .05
select value
from generate_series(0.0, 1.0, 0.05)

The results are as we’d expect.

You can also use negative values for start, end, and step, and this function is available for all the precise numeric types - tinyint, smallint, int, bigint, and decimal

TLDR

You can generate any series of numbers in SQL Server using the generate_series function.

The code is in my GitHub.

Happy hacking!