SQL Server – Does MS SQL Server Have generate_series Function?

sql serversql-server-2019

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.

Best Answer

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.

Related Question