New in SQL Server 2022 – Generate_Series

One of the new language features added in SQL Server 2022 is the GENERATE_SERIES function. This allows you to generate a


This gives me a simple sequence of numbers in a result set, with the column header, value.

2022-03-31 14_57_20-SQLQuery3.sql - ., 51433.sandbox (sa (80))_ - Microsoft SQL Server Management St

Let’s take this code from Dwain Camps article, Tally Tables in T-SQL:

DECLARE @S VARCHAR(8000) = 'Aarrrgggh!';
SELECT value, s
     -- Always choose the first element
     SELECT value=1, s=LEFT(@S, 1) UNION ALL
     -- Include each successive next element as long as it’s different than the prior
     SELECT value, CASE
         WHEN SUBSTRING(@S, value-1, 1) <> SUBSTRING(@S, value, 1)
         THEN SUBSTRING(@S, value, 1)
         -- Repeated characters are assigned NULL by the CASE
     FROM GENERATE_SERIES(start=1, stop=100)
     WHERE value BETWEEN 2 AND LEN(@S)
) a
-- Now we filter out the repeated elements

Now the original code has a CTE that generates the series, or tally table. I’ve replaced that with GENERATE_SERIES. The code works as expected, which in this case is to remove repeating characters.

2022-03-31 14_58_21-SQLQuery3.sql - ., 51433.sandbox (sa (80))_ - Microsoft SQL Server Management St

SQL Server 2022 is now out in preview and I’d urge you to give it a try. This is a neat new feature, and it does provide more standard code than the variety of ways I see people building tally tables.

I haven’t tested performance, but I am hoping it does as well as cross joining system tables or using a CTE.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.