SQL Server 2022 and the GENERATE-SERIES command .. a T-SQL language enhancement

Hi Guys,

Welcome back!

I have to admit that I like to find out what is added in each new version of SQL, especially the T-SQL language part.
There will be a lot to talk about ..

So let's start!
Today we see the first improvement of T-SQL language that the new SQL Server 2022 bring us: the GENERATE_SERIES command

The GENERATE_SERIES command

I bet that when we are asked to generate a series of numbers such as 1,2,3, .. or 2, 4,6 etc etc immediately we think of the CTE (the well-known "common table expression") or the function of ranking ROW_NUMBER.

Someone then invents some more original but still functional approach.

Let's see some ways currently used to generate a series of values:

Using a CTE:

WITH Cte AS 
(
  SELECT 1 AS x
  UNION ALL 
  SELECT x + 1 FROM Cte WHERE x < 5
)
SELECT * FROM Cte  -- remember to add the option maxrecursion...

Executing this command we will have:

 

Or we could use a ranking function:

SELECT ROW_NUMBER() OVER (Ordery by object_id) FROM sys.objects

In this case, it is necessary to rely on the rows of an existing table (which must have a sufficient number of rows)

At this point the new command comes into play!

Just type:

SELECT * FROM GENERATE_SERIES ( START = 2, STOP = 12, STEP = 2 )

...et voilà!

You can avoid to explicit that name of the parameter ( START, STOP, ETC..)

Speaking of performance!

We run a very simple benchmark generating a series of number from 1 to 1000 to compare the execution time of the two solution: GENERATE_SERIES Vs. CTE approach!

The execution time of the new GENERATE_SERIES command is twice as fast as with the CTE solution. 2015 ms Vs. 4334 ms!

Another aspect is the CPU time. The new command is a clear winner whit 47 ms. Vs. 3215 ms!

That' all for today!

Stay tuned for the next posts ... others new T-SQL commmands will follow soon...

But please follow me on linkedin!

Luca

Previous post:SQL Server 2022 and the Parameter Sensitive Plan Optimization (PSP) with example