SQL Server GENERATE_SERIES() Function

Summary: in this tutorial, you will learn how to use the SQL Server GENERATE_SERIES() function to generate a series of numbers within a specific range.

Introduction to the SQL Server GENERATE_SERIES() function

The GENERATE_SERIES() function allows you to create a series of values within a specified range.

Here’s the syntax of the GENERATE_SERIES() function:

GENERATE_SERIES(start, stop [, step])Code language: SQL (Structured Query Language) (sql)

In this syntax:

• start: The starting value of the series.
• stop: The ending value of the series. The series stops when the last generated step value exceeds the stop value.
• step: The increment or decrement value between steps in the series. The step can be either positive or negative but can’t be zero.

The start, stop, and step parameters can be a literal, a variable, or a scalar expression of type integers (tinyint, smallint, int, and bigint) or decimal ( or numeric). They must also have the same data type.

If the start is less than the stop, the step will have a default value 1. Otherwise, the step’s default value is -1.

The GENERATE_SERIES() function returns a result set that consists of a single column with the name “value“.

The GENERATE_SERIES() function returns an empty result set in the following cases:

• If the start is less than the stop, and the step has a negative value.
• If the start is greater than the stop, and the step has a positive value.
• If either the start, stop, or step is NULL.

In PostgreSQL, the GENERATE_SERIES() function generates a series of values including numbers, dates, and timestamps. However, in SQL Server, the GENERATE_SERIES() can only create a series of numbers.

SQL Server GENERATE_SERIES() function examples

Let’s explore some examples of using the GENERATE_SERIES() function.

1) Basic GENERATE_SERIES() function examples

The following example uses the GENERATE_SERIES() function to generate a series of integers between 1 and 5 in increments of 1:

SELECT value
FROM GENERATE_SERIES(1,5);Code language: SQL (Structured Query Language) (sql)

Output:

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

(5 rows affected)Code language: SQL (Structured Query Language) (sql)

The following example uses the GENERATE_SERIES() function to generate a series of integers between 1 and 10 in increments of 2:

SELECT value
FROM GENERATE_SERIES(1,10,2);Code language: SQL (Structured Query Language) (sql)

Output:

value
-----
1
3
5
7
9

(5 rows affected)Code language: SQL (Structured Query Language) (sql)

The following statement uses the GENERATE_SERIES() function to generate a series of integers between 10 and 1 in decrements of 2:

SELECT value
FROM GENERATE_SERIES(10,1,-2);Code language: SQL (Structured Query Language) (sql)

Output:

value
------
10
8
6
4
2

(5 rows affected)Code language: SQL (Structured Query Language) (sql)

The following statement uses the GENERATE_SERIES() function to generate a series of decimal values between 1 and 2 in increments of 0.1:

SELECT value
FROM GENERATE_SERIES(1.0,2.0,0.1);Code language: SQL (Structured Query Language) (sql)

Output:

value
------
1.0
1.1
1.2
1.3
1.4
1.5
1.6
1.7
1.8
1.9
2.0

(11 rows affected)Code language: SQL (Structured Query Language) (sql)

2) Using the GENERATE_SERIES() function to a series of dates

The following example uses the GENERATE_SERIES() function to generate a series of 7 date values starting from 2024-04-01:

SELECT
  CONVERT(DATE, DATEADD (DAY, VALUE, '2024-04-01')) AS DATE
FROM
  GENERATE_SERIES (0, 6);Code language: SQL (Structured Query Language) (sql)

Output:

Date
----------
2024-04-01
2024-04-02
2024-04-03
2024-04-04
2024-04-05
2024-04-06
2024-04-07

(7 rows affected)Code language: SQL (Structured Query Language) (sql)

How it works.

• First, generate a series of integers from 0 to 6 using the GENERATE_SERIES() function. These numbers represent the number of days that we add to the starting date ('2024-04-01').
• Second, for each number generated by the GENERATE_SERIES() function, add the corresponding number of days to the starting date ('2024-04-01') using the DATEADD() function.
• Third, convert the DATETIME values to DATE using the CONVERT() function.

3) Using the GENERATE_SERIES() function to calculate compound interest over years

First, create a new table called savings that stores the principal and annual interest rate:

CREATE TABLE savings(
    id INT IDENTITY PRIMARY KEY,
    principal money NOT NULL,
    interest_rate DEC(19,5) NOT NULL
);Code language: SQL (Structured Query Language) (sql)

Second, insert rows into the savings table:

INSERT INTO
  savings (principal, interest_rate)
VALUES
  (1000, 0.05),
  (5000, 0.07);Code language: SQL (Structured Query Language) (sql)

Third, retrieve data from the savings table:

SELECT * FROM savings;

Output:

id | principal | interest_rate
---+-----------+--------------
1  | 1000.0000 | 0.05000
2  | 5000.0000 | 0.07000
(2 rows)

Finally, cross join the savings table with the series of numbers generated by the GENERATE_SERIES() function to calculate the ending balance each year:

SELECT
  id,
  principal,
  interest_rate,
  VALUE,
  principal * POWER(1 + interest_rate, VALUE) balance
FROM
  savings
  CROSS JOIN GENERATE_SERIES(1, 3);Code language: SQL (Structured Query Language) (sql)

Output:

id | principal | interest_rate | VALUE | balance
---+-----------+---------------+-------+------------
1  | 1000.0000 | 0.05000       | 1     | 1050.000000
1  | 1000.0000 | 0.05000       | 2     | 1102.500000
1  | 1000.0000 | 0.05000       | 3     | 1157.630000
2  | 5000.0000 | 0.07000       | 1     | 5350.000000
2  | 5000.0000 | 0.07000       | 2     | 5724.500000
2  | 5000.0000 | 0.07000       | 3     | 6125.200000
(6 rows)Code language: SQL (Structured Query Language) (sql)

Summary

• Use the GENERATE_SERIES() function to generate a series of numbers within a specific range.