Does MS SQL Server have generate_series function

Question

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.

Answer 1

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.

enter image description here

✅ 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);

enter image description here

✅ 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] 

enter image description here

Answer 2

A fairly common method of creating a series in T-SQL consists of using a CTE as the source, something like:

WITH t AS
(
 SELECT n = v2.n * 10 + v1.n
 FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v1(n)
 CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v2(n)
)
SELECT t.n
FROM t
ORDER BY T.n;

The query above will return an ordered list of values from 0 to 99.

For dates, you can implement it like this:

WITH t AS
(
 SELECT n = v2.n * 10 + v1.n
 FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v1(n)
 CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v2(n)
)
SELECT DATEADD(DAY, t.n, '2019-01-01')
FROM t
ORDER BY T.n;

Which provides a list of dates starting at January 1st, 2019, and continuing for 100 days, ending on April 10th, 2019.

There are numerous other ways of generating a set like this, including generating a "numbers" table.

• ripvlan: Most helpful for those of us still on older versions of SQL Server. This is a portable version-agnostic solution. It's also easy to copy/paste the CROSS JOIN and get more rows back. You could also use a Recursive CTE which I believe will work all the way back to 2008. And use with OPTION(MAXRECURSION 1000) to get more rows.

Answer 3

Yes as of SQL Server 2022

Prior to this, tracking on the SQL Server feedback forum was available at this post and this post.