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!
Luca
Previous post:SQL Server 2022 and the Parameter Sensitive Plan Optimization (PSP) with example