SQL Server 2022 introduced the GENERATE_SERIES() function that allows us to create a series of values that increment between a given start and end point.

We can create a decrementing series simply by having a greater start point than the end point, while omitting the third argument.

We can also create a decrementing series by using a negative value for the third argument. This obviously requires that the start point is higher than the stop point.

Example

Here’s a basic decrementing series:

SELECT * FROM GENERATE_SERIES( 5, 1 );

Result:

value      
-----------
          5
          4
          3
          2
          1

Here, I only included the first two arguments (for the start and end points). I didn’t specify a third argument (for the step amount). Therefore, SQL Server used the default step amount, which is -1 in this case. It used the negative step amount because it’s smart enough to identify that our start point is higher than our stop point.

Here’s what it looks like when we explicitly specify the step amount:

SELECT * FROM GENERATE_SERIES( 5, 1, -1 );

Result:

value      
-----------
          5
          4
          3
          2
          1

The first argument is the value to start the series, the second argument is where the series ends, and the third argument is the step amount.

We got the same result as the previous example (because our step amount is the same as the default step amount).

Here’s another example:

SELECT * FROM GENERATE_SERIES( 50, 20, -7 );

Result:

value      
-----------
         50
         43
         36
         29
         22

When the First Argument is Less than the Second Argument

When we explicitly specify the step amount when create a decrementing series, we must be sure that the first argument is greater than the second one. Otherwise we won’t get any result, due to the fact that we can’t decrement to a higher value.

Here’s what happens when we try to do this:

SELECT * FROM GENERATE_SERIES( 20, 50, -7 );

Result:

value      
-----------

(0 rows affected)

In this case I used the previous example, but I swapped the first two arguments around. So I tried to decrement from 20 to 50. Obviously that doesn’t work, and we got no results.

When the Step Amount is Greater than the Range of the First Two Arguments

Here’s what happens when we provide a step amount that’s greater than the range of the first two arguments:

SELECT * FROM GENERATE_SERIES( 5, 1, -7 );

Result:

value      
-----------
          5

Our series consists of a single value. This is because the step amount would cause the next value in the series to be outside the range of the first two arguments.

Series Consisting of Negative Values

Our decrementing series can be made up of negative values:

SELECT * FROM GENERATE_SERIES( -20, -50, -7 );

Result:

value      
-----------
        -20
        -27
        -34
        -41
        -48

It doesn’t need to be a decrementing series in order to use negative values. We could also create an incrementing series consisting of negative values:

SELECT * FROM GENERATE_SERIES( -50, -20, 7 );

Result:

value      
-----------
        -50
        -43
        -36
        -29
        -22