SQL Server Portal

Sometimes we come across a case where we need to generate a number series in SQL Server between two numbers. For example, if you give a week’s start date and end date and you need to generate whole week’s dates, it was not easy since there was no specific function available to generate series in earlier version of SQL Server (Earlier than SQL Server 2022).

In SQL Server 2022, a new function shipped namely GENERATE_SERIES() which was a long awaited function. This function will allow us to generate series of numbers between two numbers.

• Old Approach (Earlier than SQL Server 2022):

Let me demonstrate how we used to struggle to generate number series in earlier versions of SQL Server, we used common table expression, Union All and a lot of other techniques to generate, it as shown below.

DECLARE @StartRange INT = 1;
DECLARE @EndRange   INT = 10; 

;WITH CTE AS (
     SELECT @StartRange AS Sno 
	 UNION ALL 
	 SELECT Sno+1 FROM CTE WHERE Sno+1 <= @EndRange ) 
SELECT * FROM CTE; 
GO
--OUTPUT

• New Approach (SQL Server 2022 & Above):

Compatibility Level:

Your database compatibility level MUST be 160 or higher to use this function. Given below is the query to change your database compatibility level.

 ALTER DATABASE tempdb SET COMPATIBILITY_LEVEL = 160 

Example 1:

In this example, I will show you how to generate series between two numbers with the newly introduced function GENERATE_SERIES(). This function generates the series of numbers between the start and end numbers and takes the steps needed to jump from start until end.

DECLARE @Start INT = 1;
DECLARE @Stop  INT = 10;
DECLARE @Step  INT = 1;

SELECT value FROM 
GENERATE_SERIES(@Start,@Stop,@Step);
GO
--OUTPUT

Default:

By Default, Step value is 1 in such cases, where start number is less than stop number, so we can achieve the same output as mentioned above without passing Step parameter as shown below.

DECLARE @Start INT = 1;
DECLARE @Stop  INT = 10;


SELECT value FROM 
GENERATE_SERIES(@Start,@Stop);
GO
--OUTPUT

Issue:

When you want to generate series in ascending order then DON’T put NEGATIVE value in Step parameter. Even if you put negative value in Step parameter, it will not generate error but it will not give you any result as shown below.

DECLARE @Start INT = 1;
DECLARE @Stop  INT = 10;
DECLARE @Step  INT = -1;

SELECT value FROM 
GENERATE_SERIES(@Start,@Stop,@Step);
GO
--OUTPUT

Example 2:

In this example, I will show you, how to generate number series in descending order using GENERATE_SERIES() function.

DECLARE @Start INT = 10;
DECLARE @Stop  INT = 1;
DECLARE @Step  INT = -1;

SELECT value FROM 
GENERATE_SERIES(@Start,@Stop,@Step);
GO

--OUTPUT

Default:

By Default, Step value is -1 in such cases, where stop number is less than start number, so we can achieve the same output as mentioned above without passing Step parameter as shown below.

DECLARE @Start INT = 10;
DECLARE @Stop  INT = 1;

SELECT value FROM 
GENERATE_SERIES(@Start,@Stop);
GO
--OUTPUT

Issue:

When you want to generate series in descending order then DON’T put POSITIVE value in Step parameter. Even if you put positive value in Step parameter, it will not generate error but it will not give you any result as shown below.

DECLARE @Start INT = 10;
DECLARE @Stop  INT = 1;
DECLARE @Step  INT = 1;

SELECT value FROM 
GENERATE_SERIES(@Start,@Stop,@Step);
GO
--OUTPUT

Example 3:

If we want to generate negative number series using GENERATE_SERIES() function then the query and output will be like this.

DECLARE @Start INT = -1;
DECLARE @Stop  INT = -10;
DECLARE @Step  INT = -1;

SELECT * FROM 
GENERATE_SERIES(@Start,@Stop,@Step);
GO
--OUTPUT

Example 4:

In this example, I will show you how to generate Odd numbers series using GENERATE_SERIES() function.

DECLARE @Start INT = 1;
DECLARE @Stop  INT = 10;
DECLARE @Step  INT = 2;

SELECT value FROM 
GENERATE_SERIES(@Start,@Stop,@Step);
GO
--OUTPUT

Example 5:

In this example, I will show you how to generate Even numbers series using GENERATE_SERIES() function.

DECLARE @Start INT = 0;
DECLARE @Stop  INT = 10;
DECLARE @Step  INT = 2;

SELECT value FROM 
GENERATE_SERIES(@start,@stop,@step);
GO--OUTPUT

Conclusion:

I found GENERATE_SERIES() function very useful. Earlier we used multiple functions to generate the series between two numbers but now we can easily achieve with the help of GENERATE_SERIES() function. Do let me know if you will use this function and how you found it.

Read Full Post »