Events
Mar 31, 11 PM - Apr 2, 11 PM
The ultimate Microsoft Fabric, Power BI, SQL, and AI community-led event. March 31 to April 2, 2025.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Applies to: SQL Server 2022 (16.x) Azure SQL Database Azure SQL Managed Instance SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric
Generates a series of numbers within a given interval. The interval and the step between series values are defined by the user.
GENERATE_SERIES
requires the compatibility level to be at least 160. When the compatibility level is less than 160, the Database Engine is unable to find the GENERATE_SERIES
function.
To change the compatibility level of a database, refer to View or change the compatibility level of a database.
Transact-SQL syntax conventions
GENERATE_SERIES ( start , stop [ , step ] )
The first value in the interval. start is specified as a variable, a literal, or a scalar expression of type tinyint, smallint, int, bigint, decimal, or numeric.
The last value in the interval. stop 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.
Indicates the number of values to increment or decrement between steps in the series. step 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 is returned.
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.
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.
The following examples demonstrate the syntax for calling GENERATE_SERIES
.
SELECT value
FROM GENERATE_SERIES(1, 10);
Here's the result set.
value
-----------
1
2
3
4
5
6
7
8
9
10
SELECT value
FROM GENERATE_SERIES(1, 50, 5);
Here's the result set.
value
-----------
1
6
11
16
21
26
31
36
41
46
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, @stop, @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
Events
Mar 31, 11 PM - Apr 2, 11 PM
The ultimate Microsoft Fabric, Power BI, SQL, and AI community-led event. March 31 to April 2, 2025.
Register today