One of the new language features added in SQL Server 2022 is the GENERATE_SERIES function. This allows you to generate a
SELECT * FROM GENERATE_SERIES(start=1, stop=7)
This gives me a simple sequence of numbers in a result set, with the column header, value.
Let’s take this code from Dwain Camps article, Tally Tables in T-SQL:
DECLARE @S VARCHAR(8000) = 'Aarrrgggh!';
SELECT value, s
FROM
(
-- 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
END
FROM GENERATE_SERIES(start=1, stop=100)
WHERE value BETWEEN 2 AND LEN(@S)
) a
-- Now we filter out the repeated elements
WHERE s IS NOT NULL;
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.
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.