Does MS SQL Server have generate_series function

Does MS SQL Server have Series Generating Function(s) (aka generate_series) like Postgresql has. If not is there a basic way to implement the function? Timestamp version is preferred

I found this question which is an old one. Maybe there is a better solution in new versions.

From SQL Server 2022 you are able to do

SELECT Value
FROM GENERATE_SERIES( /* START= */ 1, /* STOP= */ 100, /* STEP= */ 1)

At SQL Bits 2022 it was stated that it will also support dates as well as numbers but this doesn't seem to have made its way into the initial release of the function. But it can still be used in conjunction with DATEADD to generate dates and datetimes.

It seems to work quite well.

✅ Execution time for number generation
The below generates 10,000,000 numbers in 700 ms in my test VM (the assigning to a variable removes any overhead from sending results to the client)

DECLARE @Value INT 

SELECT @Value =[value]
FROM GENERATE_SERIES(1, 10000000)

✅ Cardinality estimates

It is simple to calculate how many numbers will be returned from the operator and SQL Server takes advantage of this as shown below.

✅ No Unnecessary Halloween Protection

In CTP 2.0 and earlier, the execution plans when inserting the result of this function to a table could have unnecessary spools. This issue now appears to have been fixed.

CREATE TABLE dbo.Numbers(Number INT PRIMARY KEY);

INSERT INTO dbo.Numbers
SELECT [value]
FROM GENERATE_SERIES(1, 10);

✅ No Unnecessary sorts or distinct-ification

In CTP 2.0 and earlier, adding an ORDER BY [value] could add a sort to the plan even when the function returned them in that order anyway. This again now seems to be fixed.

SELECT DISTINCT [value]
FROM GENERATE_SERIES(1, 10)
ORDER BY [value]

StackExchange Database Administrators Q#255165, answer score: 15