GENERATE_SERIES

Returns a single-column table holding a sequence of numbers, from a start value to a stop value, in steps of a given size. Use it in the FROM clause wherever a table of numbers is needed - a dense calendar, a fixed set of buckets, a driver that fills the gaps in a sparse table.

Syntax

GENERATE_SERIES ( start, stop [ , step ] )

Arguments

start

The first value of the series.

stop

The value the series runs towards. It is included when the step lands on it exactly, and is never passed: a range the step does not divide evenly stops at the last value still inside it.

step

The interval between consecutive values. Optional; see Default step below. Zero is an error.

All three arguments are exact numeric expressions - literals, variables, parameters, columns, or arithmetic over them - of type tinyint, smallint, int, bigint, decimal or numeric.

The arguments must all be of the same type. There is no implicit conversion between them, not even from a narrower integer type to a wider one: mixing an int with a bigint is refused rather than widened. Cast the arguments to one type instead. Within decimal and numeric, the precision and scale may differ freely - decimal(9,2) and decimal(9,1) in one call are fine.

Return value

A table of one column:

Column name	Data type	Description
value	the arguments’ common type	One row per value of the sequence, in order

An integer series returns the argument type unchanged, so a tinyint series yields tinyint values, not int ones.

A decimal or numeric series unifies the argument precisions and scales: the result scale is the largest scale of any argument, and the result precision is that scale plus the largest number of integer digits any argument can hold. So decimal(9,2) with decimal(9,1) returns decimal(10,2); decimal(9,2), decimal(9,2) and decimal(9,3) return decimal(10,3); and three uniform decimal(18,2) arguments return decimal(18,2).

The value column’s nullability follows SQL Server’s metadata behavior: decimal series are NOT NULL, while integer series are nullable when an argument’s resolved type is nullable. If every argument is an untyped NULL, the result type defaults to a nullable int; the result is still empty.

Default step

When step is omitted it follows the direction of the range: 1 when stop is greater than or equal to start, and -1 when stop is smaller. A two-argument call therefore never returns an empty result:

SELECT value FROM GENERATE_SERIES(1, 5);   -- 1, 2, 3, 4, 5
SELECT value FROM GENERATE_SERIES(5, 1);   -- 5, 4, 3, 2, 1
SELECT value FROM GENERATE_SERIES(1, 0);   -- 1, 0

The one exception is tinyint, which has no negative values. A descending two-argument tinyint series would need an implied step of -1, which the type cannot hold, so it returns no rows; and an explicit negative step cannot be typed tinyint either, which the same-type rule then refuses. A tinyint series only ever ascends.

Remarks

A NULL argument yields an empty result, not an error. If start, stop or step evaluates to NULL - written as NULL, or arriving through a variable or a column - the series is empty and the query succeeds.

A step of 0 is an error, raised even when start and stop are equal: Argument value 0 is invalid for argument 3 of generate_series function. A literal zero, or a zero supplied through a parameter, is rejected before the statement runs.

A zero step taken from a correlated source column is the one case that behaves differently, and it does so on every source: the value is not known until the driving row is read. On a source that runs the series itself the server raises 4199, but the error reaches the client only after the result set has been closed, so a client that reads rows until there are none sees an empty result and a successful query; on a source that needs the rewrite, no error is raised at all. Either way the whole batch of driving rows comes back empty, not only the offending one. Read the next result set to surface the error.

A step running against the range yields no rows. GENERATE_SERIES(1, 10, -1) returns nothing, because the series can never reach 10 going down. A step larger than the whole range returns exactly one row, the start value.

Arguments are evaluated once per call, so a non-deterministic argument such as RAND() gives one series that is internally consistent, not a bound that shifts as rows are produced.

A column argument requires APPLY. To take the bounds of the series from each row of another table, use CROSS APPLY or OUTER APPLY; a correlated column reference in a plain FROM cannot be bound.

Querona treats decimal and numeric as one type, so a call mixing the two spellings is accepted here while SQL Server refuses it. Every other type combination is refused exactly as SQL Server refuses it.

Where the series is evaluated

GENERATE_SERIES produces its rows wherever that costs least, and which form is sent to a SQL Server source follows the dialect version of the connection to it.

• On its own, or in a database that is not backed by SQL Server, Querona generates the rows itself. They are streamed, never materialized, so a TOP (5) over a million-row series stops after five values instead of building a million.

• Together with a SQL Server source - joined with one of its tables, or simply queried from a database that source backs - the function is evaluated by that server, so no rows travel between Querona and the source. When the connection’s dialect version is SQL Server 2022 or later, Querona sends the server its own GENERATE_SERIES call. On an older dialect version it sends an equivalent rewrite instead: a self-contained subquery that numbers rows drawn from a handful of cross-joined constant rows, and derives the series values from that number. The rewrite uses only long-established SQL Server syntax, so it runs on every SQL Server version Querona connects to, and it returns the rows the native call would.

• With bounds taken from another table’s rows - the CROSS APPLY / OUTER APPLY form - the series is evaluated by the source of that table when it can run the function, otherwise by the default federator when it can, and otherwise by Querona itself, once per driving row. The last case is what makes the form work over any source, for example a PostgreSQL table.

The native call and the rewrite both stream, and both follow the row goal of a TOP, so a ceiling that is never reached costs nothing. At extreme row counts the difference is throughput: measured over 100 million rows, the native call uses roughly 3.5 to 5 times less CPU per row than the rewrite. At the series lengths most queries use - days in a year, buckets in a report - the difference is not observable.

Note

SQL Server resolves GENERATE_SERIES only in a database running at compatibility level 160 or higher, whatever the server version. A connection whose dialect version is SQL Server 2022 or later but whose database runs below that level therefore fails the statement at the source with Invalid object name ‘GENERATE_SERIES’. Raise the database’s compatibility level, or set the connection’s dialect version to SQL Server 2019 so Querona sends the rewrite instead.

Limitations

Note

These are current limitations. See Feature support for the wider picture of what is and is not supported.

• A decimal series joined with tables needs a SQL Server 2022 connection. The rewrite covers the integer types only, so a decimal or numeric series can be pushed to a source only when that connection’s dialect version is SQL Server 2022 or later. Against an older dialect version the join is refused while it is planned, with Statement cannot be federated, because no available engine supports required SQL features. A standalone decimal series works everywhere.

• A series whose span or inclusive row count exceeds bigint can fail through the rewrite. The rewrite computes both the distance and distance / step + 1 as bigint. Either calculation can overflow: the full minimum-to-maximum bigint span overflows while subtracting the bounds, and GENERATE_SERIES(0, 9223372036854775807) overflows when adding the inclusive final row. A natively capable server starts streaming both instead. Either request contains about 9.2 quintillion rows or more, so it could not finish in practice.

Examples

A dense calendar - one row per day of a year - which is what the function is most often used for:

SELECT DATEADD(DAY, value, '2026-01-01') AS d
FROM GENERATE_SERIES(0, 364)
ORDER BY value;

Ascending, descending and stepped series:

SELECT value FROM GENERATE_SERIES(1, 10, 3) ORDER BY value;    -- 1, 4, 7, 10
SELECT value FROM GENERATE_SERIES(10, 1, -3) ORDER BY value;   -- 10, 7, 4, 1
SELECT value FROM GENERATE_SERIES(5, 5) ORDER BY value;        -- 5

Filtering and shaping the series like any other table:

SELECT value * 2 AS doubled
FROM GENERATE_SERIES(1, 10)
WHERE value % 2 = 0
ORDER BY value;

Only the first rows of a long series; nothing beyond them is produced:

SELECT TOP (5) value FROM GENERATE_SERIES(1, 1000000);

Joined with a SQL Server table, which that server evaluates as one query:

SELECT g.value, t.id
FROM dbo.orders t
JOIN GENERATE_SERIES(1, 10000) g ON g.value = t.id
ORDER BY g.value;

Bounds taken from each row, which needs CROSS APPLY:

SELECT t.id, s.value
FROM dbo.orders t
CROSS APPLY GENERATE_SERIES(1, t.line_count) s
ORDER BY t.id, s.value;

See Also

• OPENJSON

• OPENROWSET

• Functions