SQL SERVER 2022 – GENERATE_SERIES Function

Recently, during a Comprehensive Database Performance Health Check for a client, I encountered a stored procedure that generated a series of numbers for a client application. The stored procedure was performing poorly and slowing down the overall application performance.  Recognizing an opportunity for optimization, we replaced the original series generation logic in the stored procedure with the GENERATE_SERIES function. The results were impressive. The new version of the stored procedure, leveraging GENERATE_SERIES, showed noticeable performance improvements, positively impacting the overall application performance.

Understanding the GENERATE_SERIES Function

In SQL Server 2022 and later versions, the GENERATE_SERIES function enables us to create a series of numbers within a given range. The user can define this range and the step between the series values, offering a high degree of flexibility.

It’s important to note that GENERATE_SERIES requires the compatibility level of SQL Server to be at least 160. If your database compatibility level is less than 160, SQL Server won’t recognize this function.

The syntax for GENERATE_SERIES is:

GENERATE_SERIES ( start, stop [, step ] )

Here, start represents the first value in the interval, stop is the last value in the interval, and step indicates the increment or decrement between steps in the series.

Examples and Use Cases of function

Let’s take a look at some examples:

SELECT value
FROM GENERATE_SERIES(1, 10);

Results:
value
-----------
1
2
3
4
5
6
7
8
9
10

SELECT value
FROM GENERATE_SERIES(1, 50, 5);

Results:
value
-----------
1
6
11
16
21
26
31
36
41
46

SELECT value
FROM GENERATE_SERIES(0.0, 1.0, 0.1);

Results:
value
---------------------------------------
0.0
0.1
0.2
0.3
0.4
0.5
0.6
0.7
0.8
0.9
1.0

SELECT value
FROM GENERATE_SERIES(100, 10, -10);

Results:
value
-----------
100
90
80
70
60
50
40
30
20
10

Some examples of using GENERATE_SERIES:

• Populate a numbers or tally table
• Generate test data with sequential values
• Loop through a set of numbers without a cursor
• Sequence IDs in a data load
• Anywhere you need an ordered set of numbers!

Well, let me know what you think of this new feature of SQL Server 2022.

Do not forget to subscribe to my youtube channel.

Reference: Pinal Dave (https://blog.sqlauthority.com)