Generate series function in SQL server

The GENERATE_SERIES function is a table-valued function that returns a single-column table named value. SQL Server introduced the built-in GENERATE_SERIES function in SQL Server 2022 to create a sequence of numbers. For older versions of SQL Server, we must use alternative methods like recursive CTEs. 

GENERATE_SERIES() is Inline table-valued function with no spool no recursion and it does not use tempdb. Execution plan uses Sequence Project operator. Meaning SQL Server generates rows on-the-fly during execution. This is why it uses almost zero memory and supports parallel plans and much faster than recursive CTEs.

Syntex

GENERATE_SERIES ( start_value, stop_value [, step_value] )

Ø  start_value: The beginning value of the series (required).

Ø  stop_value: The ending value of the series (required). The series stops when the next value would exceed the stop value.

Ø  step_value: The increment between each successive value (optional). The default is 1. 

See the example,

Generate the numeric series

SELECT value FROM GENERATE_SERIES(1, 10);

By default, it increases 1 by 1. If we want to increases more that one then we need to use 3^rd optional value

SELECT value FROM GENERATE_SERIES(1, 10,2);

The above example is in ascending order. We can also generate the number in descending order.

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

We can also generate the date series using this function.

FROM GENERATE_SERIES(0, 3651) v; DECLARE @StartDate DATE = '2025-01-01'; DECLARE @EndDate   DATE = '2025-12-31';  SELECT     EOMONTH(DATEADD(MONTH, value, @StartDate)) AS SeriesDate FROM GENERATE_SERIES(        0,        DATEDIFF(MONTH, @StartDate, @EndDate) );