GENERATE_SERIES (Transact-SQL)
Applies to: SQL Server 2022 (16.x) Preview
Azure SQL Edge
Generates a series of numbers within a given interval. The interval and the step between series values are defined by the user.
Transact-SQL Syntax Conventions
Syntax
GENERATE_SERIES
(
START = @start | start_literal | numeric_expression
, STOP = @stop | stop_literal | numeric_expression
[, STEP = @step | step_literal | numeric_expression ]
)
Arguments
START
The START
parameter is the first value in the interval. The START
parameter is specified as a variable, a literal, or a scalar expression of type tinyint, smallint, int, bigint, decimal, or numeric.
STOP
The STOP
parameter is the last value in the interval. The STOP
parameter is specified as a variable, a literal, or a scalar expression of type tinyint, smallint, int, bigint, decimal, or numeric. The series stops once the last generated step value exceeds the STOP
value.
The data type for STOP
must match the data type for START
.
[ STEP ]
The STEP
parameter indicates the number of values to increment or decrement between steps in the series. The STEP
parameter is an expression of type tinyint, smallint, int, bigint, decimal, or numeric. STEP
can be either negative or positive, but can't be zero (0
)).
This argument is optional. The default value for STEP
is 1 if START
is less than STOP
, otherwise, the default value is -1 if START
is greater than STOP
.
If START
is less than STOP
and a negative value is specified for STEP
, or if START
is greater than STOP
and a positive value is specified for STEP
, an empty result set will be returned.
Return types
Returns a single-column table containing a sequence of values in which each differs from the preceding by STEP
. The name of the column is value
. The output is the same type as START
and STOP
.
Permissions
No permissions are required for GENERATE_SERIES
; however, the user needs EXECUTE
permission on the database, and permission to query any data that is used as inputs.
Examples
The following examples demonstrate the syntax for calling GENERATE_SERIES
.
A. Generate a series of integer values between 1 and 100 in increments of 1 (default)
SELECT value
FROM GENERATE_SERIES(START = 1, STOP = 10);
Here's the result set.
value
-----------
1
2
3
4
5
6
7
8
9
10
B. Generate a series of integer values between 1 and 50 in increments of 5
SELECT value
FROM GENERATE_SERIES(START = 1, STOP = 50, STEP = 5);
Here's the result set.
value
-----------
1
6
11
16
21
26
31
36
41
46
C. Generate a series of decimal values between 0.0 and 1.0 in increments of 0.1
DECLARE @start decimal(2, 1) = 0.0;
DECLARE @stop decimal(2, 1) = 1.0;
DECLARE @step decimal(2, 1) = 0.1;
SELECT value
FROM GENERATE_SERIES(START = @start, STOP = @stop, STEP = @step);
Here's the result set.
value
---------------------------------------
0.0
0.1
0.2
0.3
0.4
0.5
0.6
0.7
0.8
0.9
1.0
See also
Feedback
Submit and view feedback for